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)

[RedSky001]

Try using:

RangeSum (above(TOTAL sum(SUMMARY.Quantity),0,RowNo(TOTAL)))

[#3]

Hi MSheraton,

The only problem with this formula is that the rolling sum does not reset to zero when the color or the style changes.

SUMMARY.body	SUMMARY.style	Color	Datex		SUMMARY.Account	Size	Size0	Size1	Size2	Size3	Size4	Size5	Size6	Size7	Size8	Size9	Size10	Size11	Size12	Size13	Size14	Size15
5351N	BC5351	003	9/5/2012	Qty	AR		0	0	0	968	1936	1936	968	0	0	0	0	0	0	0	0	0
				Rolling Qty	AR		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
		004	9/6/2012	Qty			0	0	0	756	1387	1409	741	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	2221	4107	4182	2178	0	0	0	0	0	0	0	0	0
		005	9/6/2012	Qty			0	0	0	316	644	708	341	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	2537	4751	4890	2519	0	0	0	0	0	0	0	0	0
		006	9/6/2012	Qty			0	0	0	660	1233	1233	741	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	3197	5984	6123	3260	0	0	0	0	0	0	0	0	0
		007	9/6/2012	Qty			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	3197	5984	6123	3260	0	0	0	0	0	0	0	0	0
		009	9/6/2012	Qty			0	0	0	956	1778	1820	910	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	4153	7762	7943	4170	0	0	0	0	0	0	0	0	0
		017	9/5/2012	Qty	AR		0	0	0	668	1336	1336	668	0	0	0	0	0	0	0	0	0
				Rolling Qty	AR		0	0	0	4821	9098	9279	4838	0	0	0	0	0	0	0	0	0
			9/6/2012	Qty			0	0	0	0	131	134	0	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	4821	9229	9413	4838	0	0	0	0	0	0	0	0	0
		024	9/6/2012	Qty			0	0	0	768	1452	1451	771	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	5589	10681	10864	5609	0	0	0	0	0	0	0	0	0
		099	9/6/2012	Qty			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
				Rolling Qty			0	0	0	5589	10681	10864	5609	0	0	0	0	0	0	0	0	0

For example when color changes from 003 to 004 the rolling Qty for color 004 should be 756 for size 3 and NOT 2221. It almost as if I want to use TOTAL but depend on the color as my breakdown.

Thanks,

Grif

[RedSky001]

Hi Grif,

I'm confused to what you're trying to achive. 

Can you create an example table showing exactly how you'd expect the pivot table to look with the data you've already provided?

Mark

[whiteline]

=aggr(RangeSum (above(sum(SUMMARY.Quantity),0,RowNo())), SUMMARY.body, SUMMARY.style, Color, Datex)

[#6]

Hi Whiteline,

I tried your expression however its not giving me the result I want. Please refere to attached document.

Grif

[#7]

Hi Mark,

I've attached the document. Please have a look at the top table. This is the outcome I want however I'm missing a Type dimension since when I add this dimention the expression doesn't work.

Grif.

[RedSky001]

Hi Grif,

whiteline gave the correct answer.

You incorrectly implmented it as

=aggr(RangeSum (above(sum(SUMMARY.Quantity),0,RowNo())), SUMMARY.body, SUMMARY.style, SUMMARY.color, SUMMARY.Date, SUMMARY.TransType,SUMMARY.SizeCode)

Try it again but drop  (SUMMARY.TransType,SUMMARY.SizeCode)

ie:

AGGR(RangeSum (above(sum(SUMMARY.Quantity),0, RowNo())),SUMMARY.body,SUMMARY.style,SUMMARY.color,SUMMARY.Date)

Mark

[whiteline]

Unfortinately, it seems that it doesn't work correctly.

I think because of using above() with aggr. It arragens the table some other way than the pivot does it.

For example it always uses load order to sort data.

Alos as I understand to drop SUMMARY.SizeCode dimension is not the case.

[#10]

I tried dropping SizeCode and Type but it does not produce the desired result.

Whiteline if Pivot table doesn't work well with aggr then is there a way to get similar table (maybe a simple table)

Grif