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.
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).
