topic Pivot table rolling sum with multiple dimensions in QlikView

Pivot table rolling sum with multiple dimensions

Fri, 07 Sep 2012 14:35:42 GMT

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
			9/5/2012	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
			9/6/2012	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
			9/5/2012	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
			9/6/2012	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)

Re: Pivot table rolling sum with multiple dimensions

RedSky001 — Fri, 07 Sep 2012 15:46:22 GMT

Try using:

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

Re: Pivot table rolling sum with multiple dimensions

Fri, 07 Sep 2012 19:38:52 GMT

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
			9/5/2012	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
			9/6/2012	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
		004	9/6/2012	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
		005	9/6/2012	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
		006	9/6/2012	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
		007	9/6/2012	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
		009	9/6/2012	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
			9/5/2012	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
			9/6/2012	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
		024	9/6/2012	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
		099	9/6/2012	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

Re: Pivot table rolling sum with multiple dimensions

RedSky001 — Fri, 07 Sep 2012 21:06:49 GMT

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

Re: Pivot table rolling sum with multiple dimensions

whiteline — Sat, 08 Sep 2012 09:11:49 GMT

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

Re: Pivot table rolling sum with multiple dimensions

Sat, 08 Sep 2012 18:41:05 GMT

Hi Whiteline,

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

Grif

Re: Pivot table rolling sum with multiple dimensions

Sat, 08 Sep 2012 18:43:10 GMT

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.

Re: Pivot table rolling sum with multiple dimensions

RedSky001 — Sun, 09 Sep 2012 10:13:34 GMT

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

Re: Pivot table rolling sum with multiple dimensions

whiteline — Sun, 09 Sep 2012 10:23:04 GMT

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.

Re: Pivot table rolling sum with multiple dimensions

Sun, 09 Sep 2012 17:23:01 GMT

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

Re: Pivot table rolling sum with multiple dimensions

whiteline — Sun, 09 Sep 2012 17:41:03 GMT

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.

Re: Pivot table rolling sum with multiple dimensions

Tue, 21 May 2013 07:00:28 GMT

Hi,

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

Thanks,