Pivot table rolling sum with multiple dimensions

[#1]

Hi all,

I have following pivot table.

Body	Style	Color	Date		Type	Size	Size0	Size1	Size2	Size3	Size4	Size5	Size6	Size7	Size8	Size9	Size10	Size11	Size12	Size13	Size14	Size15
5351N	BC5351	003	9/5/2012	Qty	POS		0	0	0	968	1936	1936	968	0	0	0	0	0	0	0	0	0
				Rolling Qty	POS		0	0	0	968	1936	1936	968	0	0	0	0	0	0	0	0	0
			9/6/2012	Qty	SM		0	0	0	497	784	837	469	0	0	0	0	0	0	0	0	0
				Rolling Qty	SM		0	0	0	497	784	837	469	0	0	0	0	0	0	0	0	0

I would like to get rolling sum working. Im using following expressions so that at each date I have the Rolling Quantity Sum.

Qty: sum(SUMMARY.Quantity)

RollingQty: RangeSum (above(sum(SUMMARY.Quantity),0,RowNo()))

These formulas work when I don't use Type as Dimension.

Body	Style	Color	Date	Size	Size0	Size1	Size2	Size3	Size4	Size5	Size6	Size7	Size8	Size9	Size10	Size11	Size12	Size13	Size14	Size15
5351N	BC5351	003	9/5/2012	Qty	0	0	0	968	1936	1936	968	0	0	0	0	0	0	0	0	0
				Rolling Qty	0	0	0	968	1936	1936	968	0	0	0	0	0	0	0	0	0
			9/6/2012	Qty	0	0	0	497	784	837	469	0	0	0	0	0	0	0	0	0
				Rolling Qty	0	0	0	1465	2720	2773	1437	0	0	0	0	0	0	0	0	0

I realize that adding Type changes the way RowNo() behaves since we always get 1 as return value. I've tried sum(Total Summary.Quantity) however that doesn't have the outcome I'm expecting since at the next body/style I need to restart the rolling sum.

All help is appreciated.

Thanks,

Grif

NOTE: Values in Green are Orders, values in black are On hand and values in red are sales (not shown)

[whiteline]

There is always a rather complex solution.

You can try to create  'accumulative calendar' (diagonal matrix with dates).

But in your case it would a bit harder than usual, because you want two expressions.

Pivot is fine, aggr and inter-record functions not always working as expected.

[#3]

Hi,

Did you get this resolved?  I'm having a similar issue.

Thanks,

DJ