topic How to calculate a cumulative field with group by? in QlikView

How to calculate a cumulative field with group by?

Thu, 01 Sep 2016 12:37:55 GMT

Hello,

I really need help with figuring out how to create a cumulative field for this case:

Order Item Key	Order	Item	Quantity Ordered	Quantity Arrived	Arrival Date	Cumulative Quantity for Item per Order
1a	1	a	50	25	28/08/2016	25
1a	1	a	50	25	31/08/2016	50
2b	2	b	6000	100	02/08/2016	100
2b	2	b	6000	150	05/08/2016	250
2b	2	b	6000	200	09/08/2016	450
2b	2	b	6000	150	15/08/2016	600
2b	2	b	6000	150	22/08/2016	750
2c	2	c	500	150	05/08/2016	150
2c	2	c	500	150	15/08/2016	300
2c	2	c	500	100	25/08/2016	400
3d	3	d	2000	500	16/08/2016	500
3d	3	d	2000	500	17/08/2016	1000
3d	3	d	2000	500	18/08/2016	1500
3d	3	d	2000	500	19/08/2016	2000

The purpose is to calculate the time that takes for X% of an Order to arrive ('X' will be dynamic).

My question is how to calculate the Cumulative Quantity for Item per Order

when my keys will be Order + Item.

I tried this:

MYtable:

LOAD

if([Order Item Key] = previous([Order Item Key]) , rangesum([Quantity Arrived], peek([Cumulative Quantity])),[Quantity Arrived]) as [Cumulative Quantity]

RESIDENT OrdersTable

ORDER BY [Order Item Key],[Arrival Date];

But the "Cumulative Quantity" creates multiplication in the table and i can't find the solution.

I also tried with 'group by' (order, item) but it didn't work here because i needed more fields except these i used for the group by.

Thank you all!

Inbar.

Re: How to calculate a cumulative field with group by?

sunny_talwar — Thu, 01 Sep 2016 15:34:03 GMT

Try this:

Table:

LOAD [Order Item Key],

Order,

Item,

[Quantity Ordered],

[Quantity Arrived],

[Arrival Date]

FROM

[https://community.qlik.com/thread/231232]

(html, codepage is 1252, embedded labels, table is @1)

Where Len(Trim([Order Item Key])) > 0;

FinalTable:

LOAD *,

If([Order Item Key] = Previous([Order Item Key]), RangeSum(Peek('Cumulative Quantity for Item per Order'), [Quantity Arrived]), [Quantity Arrived]) as [Cumulative Quantity for Item per Order]

Resident Table

Order By [Order Item Key], [Arrival Date];

DROP Table Table;

Re: How to calculate a cumulative field with group by?

Sun, 04 Sep 2016 05:37:09 GMT

Thank you for this suggestion,

but the 'if' still duplicates the rows in the second table.

Re: How to calculate a cumulative field with group by?

sunny_talwar — Sun, 04 Sep 2016 10:18:05 GMT

Duplicates the row? I am not sure I understand your concern here. What exactly do you mean here? Is this something happening in the sample or is this something you are seeing in your actual application?

Re: How to calculate a cumulative field with group by?

Sun, 04 Sep 2016 10:30:50 GMT

in my actual application.

"Table1" works just fine but "Table" multiplies each row several times

Table1:

LOAD

Company,

[ID],

[Order Item Key],

[Arrival Date],

[Quantity Arrived],

[Type],

[Order Item],

[Receive Doc No],

FROM "$(QvdPath)TEMP.qvd"(qvd);

WHERE Len(Trim([Order Item])) > 0;

Table:

Load

If([Order Item Key] = Previous([Order Item Key]) AND [Type] = 'blabla', RangeSum(Peek('Cumulative Quantity'), [Quantity Arrived]), [Quantity Arrived]) as [Cumulative Quantity]

Resident Table1

ORDER BY [Order Item Key],[Arrival Date];

DROP TABLE Table1;

Re: How to calculate a cumulative field with group by?

sunny_talwar — Mon, 05 Sep 2016 14:13:11 GMT

Not sure how you can you get more number of rows than your resident table. How many observations increase when you do this?

Re: How to calculate a cumulative field with group by?

Anil_Babu_Samineni — Mon, 05 Sep 2016 14:24:08 GMT

Are you getting problem here

RangeSum(Peek('Cumulative Quantity'), [Quantity Arrived]), [Quantity Arrived]) as [Cumulative Quantity]

if Yes,

Perhaps this

RangeSum('Cumulative Quantity', Peek([Quantity Arrived]), [Quantity Arrived]) as [Cumulative Quantity]

Re: How to calculate a cumulative field with group by?

Wed, 07 Sep 2016 05:27:24 GMT

It's really weird

when i'm using the same table in a new file it works just fine

but when i run it on my whole Module each row increases different number of times. it can be 11 or 96.

Re: How to calculate a cumulative field with group by?

Wed, 07 Sep 2016 05:28:06 GMT

Thank you, but i need the opposite

Re: How to calculate a cumulative field with group by?

sunny_talwar — Wed, 07 Sep 2016 12:37:07 GMT

How exactly are you using this? Would you be able to show screenshots?