topic Cumulative sum 'grouped by' in load script ? in QlikView

Cumulative sum 'grouped by' in load script ?

Fri, 21 Sep 2012 14:14:20 GMT

Hello,

I would like to load a table with cumulative stock value per date, material, plant. (a kind of running-total group by or like an Oracle 'over partition' function)

I have only movement (goods issue and goods receipt qty) by plant, material, date...in SAP source, and i have the starting qty for the same dimensions...

so the cumulative value per material, plant, date... would be the stock start value + sum of all mvts (GR-GI) up to the current processed date in the load script....

This cumulative value represents the value of the stock for this date, this plant, this material...

In a graph, it would be something like :rangesum(above(total sum(Stock_Variance), 0, rowno(Total)))

If I could load it in a table with a script, It would be easier to retrieve the stock value for any period selected...

Is there any way to use the rangesum function in a script with 'group by' features ?

Many thanks for your support,

Chris

Re: Cumulative sum 'grouped by' in load script ?

Fri, 21 Sep 2012 15:00:52 GMT

Instead of above, you can use peek in your script.

Alse check this topic:

http://community.qlik.com/message/80305#80305

Re: Cumulative sum 'grouped by' in load script ?

jagan — Fri, 21 Sep 2012 15:27:13 GMT

Hi,

Try this script

LINE_ITEMS:

LOAD * INLINE [

LINE_ITEM_ID, DATE_INTEGER, QUANTITY, PRICE

3764793, 41129411956019, 1, 4376

3764793, 41129457546296, 1, 356

3764793, 41129481006944, 1, 4019

3815241, 41148484849537, 1, 312.00

3815241, 41149623449074, 1, 329.00

3815241, 41150513599537, 1, 312.00

3728772, 41151676979167, 1, 104.67

3728772, 41136601608796, 3, 104.67

3764793, 41129645358796, 4, 1004

3728772, 41134304803241, 4, 287.85

];

TEST:

LOAD

LINE_ITEM_ID,

DATE_INTEGER,

Sum(PRICE) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative')) AS Cumulative

Resident LINE_ITEMS

GROUP BY LINE_ITEM_ID, DATE_INTEGER;

DROP TABLE LINE_ITEMS;

Hope this helps you.

Regards,

Jagan.