Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Month | Product | Qty |
---|---|---|
1/1/2013 | A | 100 |
2/1/2013 | A | 120 |
3/1/2013 | A | 85 |
4/1/20/13 | A | 110 |
5/1/2013 | A | 100 |
1/1/2013 | B | 75 |
2/1/2013 | B | 85 |
3/1/2013 | B | 95 |
4/1/2013 | B | 125 |
5/1/2013 | B | 135 |
6/1/2013 | B | 120 |
7/1/2013 | B | 110 |
Product | 1/1/2013 | 2/1/2013 | 3/1/2013 | 4/1/2013 | 5/1/2013 | 6/1/2013 | 7/1/2013 |
---|---|---|---|---|---|---|---|
A | 100 | 220 | 305 | 415 | 515 | 515 | 515 |
B | 75 | 160 | 255 | 380 | 515 | 635 | 745 |
Product | 1/1/2013 | 2/1/2013 | 3/1/2013 | 4/1/2013 | 5/1/2013 | 6/1/2013 | 7/1/2013 |
---|---|---|---|---|---|---|---|
A | 100 | 220 | 305 | 415 | 515 | - | - |
B | 75 | 160 | 255 | 380 | 515 | 635 | 745 |
I would then want to calculate return percentages by month using the running total like below assuming my retrurn data looked like this;
Returns:Product | 1/1/2013 | 2/1/2013 | 3/1/2013 | 4/1/2013 | 5/1/2013 | 6/1/2013 | 7/1/2013 |
---|---|---|---|---|---|---|---|
A | 3 | 6 | 2 | 6 | 1 | 7 | 4 |
B | 2 | 6 | 4 | 5 | 6 | 2 | 1 |
Return percentages:
Product | 1/1/2013 | 2/1/2013 | 3/1/2013 | 4/1/2013 | 5/1/2013 | 6/1/2013 | 7/1/2013 |
---|---|---|---|---|---|---|---|
A | 3.0% | 2.7% | 0.7% | 1.4% | 0.2% | 1.4% | 0.8% |
B | 2.7% | 3.8% | 1.6% | 1.3% | 1.2% | 0.3% | 0.1% |
Any ideas? Is it even possible to do this in a pivot table? I haven't found any way to do so.
Regards,John
See attached qvw for two options.
Thanks again, Gysbert. That will certainly work in my data model. I even think I can figure out how to apply it to my data that is broken down into SubProducts like A1, A2, A3 and B1, B2, B3, etc.
The only thing you did not address is the return data. When I add that, and/or using sub-products and make the field names the same, I get synthetic joins. I could concatenate the Month, Product and SubProduct (or hash them) to create a link between ship and returns, but haven't figured out where to create the link field (unless I do another resident load).
Would a calendar be a better solution?
--john
Probably easiest to concatenate the fact tables so Qty and Returns end up in one table. This blog post may help too: Fact Table with Mixed Granularity