How to calculate a cumulative field with group by?

[#1]

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.

[sunny_talwar]

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;

[#3]

Thank you for this suggestion,

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

[sunny_talwar]

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?

[#5]

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;

[sunny_talwar]

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

[Anil_Babu_Samineni]

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]

[#8]

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.

[#9]

Thank you, but i need the opposite

[sunny_talwar]

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