Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to force a 0 for skus that don't have sales
- Thread starter shricky
- Start date
Some thoughts:
You want to display both transactions that exist and those that could have existed. It is quite well possible if you go for a cross join/union:
If you have 10 SKU's and 100 customers then a cross join between SKU and Customer dimensions will yield 10*100 = 1000 records. Define a sales of 0 (zero) for these records and union this query with the query that fetches the sales for the existing SKU/customer combinations.
Apply the correct aggregation to the sales item and the result should be 1000 rows with:
1. Actual sales
2. Sales = 0 for those combo without any sales
Ties Blom
You want to display both transactions that exist and those that could have existed. It is quite well possible if you go for a cross join/union:
If you have 10 SKU's and 100 customers then a cross join between SKU and Customer dimensions will yield 10*100 = 1000 records. Define a sales of 0 (zero) for these records and union this query with the query that fetches the sales for the existing SKU/customer combinations.
Apply the correct aggregation to the sales item and the result should be 1000 rows with:
1. Actual sales
2. Sales = 0 for those combo without any sales
Ties Blom
- Status
Similar threads
