Running Total in Pivot Table

[johnca]

Okay, Gysbert got me on the road with a nice solution (seems so obvious now, but I digfress...), but now I have another dilemna. I need to maintain a running total within a pivot table, with data a la below. A roduct will ship for a time then stop, but it will continue to be returned. I need to be able to calculate a return percentage of the total shipped long after it stops shipping, by month.

Here's an example of my data, already sorted by Product and Month;

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

In my script I can make a running total using If(Previous(Product)=Product,RangeSum(Peek('RunningTotal'),Qty),Qty) as RunningTotal

That, however, results in a running total only up to the point where a product stops shipping.

In a straight table, I can use:

RangeSum(Above(Sum(Qty),0,RowNo()))

-or-

RangeSum(Above(Sum(Qty),0,NoOfRows()))

to get a running total, and it works fine, but, I need to do this in a horizontal fashion in a pivot table like below, so that Product A's total continues through to the current month;

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

But the best I can get is the following where the running total stops for product A;

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

[Gysbert_Wassenaar]

See attached qvw for two options.

[johnca]

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

[Gysbert_Wassenaar]

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