Cumulative sum in script

[#1]

Hi Qlikers,

i have a problem simple to understand but difficult to solve for me.

I have a Warehouse movements table with the movements of one year back (MOVEMENTS_1YB).

I need to make a cumulative sum of QTA_MOV_1YB grouped by CAUSAL_MOV_1YB,DATE_MOV_1YB and ITEM and ordered by DATE_MOV_1YB.

My goal is produce a field within the script called CUM_QTA_MOV_1YB.

I've attached the script that produce an error ('INVALID EXPRESSION') now.

MOVEMENTS_1YB:

LOAD

     ITEM,

     QTA_MOV_1YB,

     DATE_MOV_1YB,

     CAUSAL_MOV_1YB

RESIDENT WAREHOUSE_MOVEMENTS

WHERE DATE_MOV_1YB>=ADDMONTHS(DATE(TODAY()),-$(ROLLING_PERIOD));

CUMULATIVE_SUM:

LOAD

     ITEM,

     DATE_MOV_1YB,

     CAUSAL_MOV_1YB,

     SUM(QTA_MOV_1YB)+PEEK(CUM_QTA_MOV_1YB)        AS CUM_QTA_MOV_1YB

RESIDENT MOVEMENTS_1YB

GROUP BY CAUSAL_MOV_1YB,DATE_MOV_1YB,ITEM;

DROP TABLE MOVEMENTS_1YB;

Anybody can help me?

Thanks in advance.

[vinieme12]

do you really need peek()??

just this would do

CUMULATIVE_SUM:

LOAD

     ITEM,

     DATE_MOV_1YB,

     CAUSAL_MOV_1YB,

     SUM(QTA_MOV_1YB)    AS CUM_QTA_MOV_1YB

RESIDENT MOVEMENTS_1YB

GROUP BY CAUSAL_MOV_1YB,DATE_MOV_1YB,ITEM;

DROP TABLE MOVEMENTS_1YB;

[Oleg_Troyansky]

Hi Michele,

in fact, you need a combination of peek() and previous(). With previous(), you can compare the current values of Causal, Date, and Item with the previous values and only if they are the same, then you'd accumulate the values, otherwise you'd start from zero.

Something like this:

IF(Previous(Causal)=Causal and Previous(Date)=Date and Previous(Item) = Item, peek('Cumulative_Qty') + Qty, Qty) as Cumulative_Qty

Notice that Previous() doesn't require single quote around the field name, while Peek() does. There is a reason for using Previous() in your comparison and Peek() in the calculation. I'm describing the process with a lot of detail in my book QlikView Your Business, also using this technique for the Inventory Analysis - check it out!

cheers,

Oleg Troyansky

[ahaahaaha]

Hi Michele,

Try

CUMULATIVE_SUM:

LOAD

    ITEM,

    DATE_MOV_1YB,

    CAUSAL_MOV_1YB,

   RangeSum(QTA_MOV_1YB, Peek('CUM_QTA_MOV_1YB')) as CUM_QTA_MOV_1YB

RESIDENT MOVEMENTS_1YB

GROUP BY CAUSAL_MOV_1YB,DATE_MOV_1YB,ITEM;

DROP TABLE MOVEMENTS_1YB;

Regards,

Andrey

[Oleg_Troyansky]

One more thought... I just noticed your GROUP BY clause. If you just need to aggregate all transactions by these three fields, then you don't need to use Peek() and Previous(). I assumed from your description that you need to know the CUMULATIVE value at the time of each individual transaction (that's common in Inventory Analysis). If you need that, then you don't need to use GROUP BY - just reload the data, sorted by your 3 key fields and a timestamp, and use Peek() and Previous() for the CUMULATIVE calculation.

Cheers,

Oleg Troyansky

[Oleg_Troyansky]

Folks,

Peek() won't work in combination with GROUP BY - you can only use one of the two, but not both techniques at the same time.

[#7]

Unfortunately it doesn't work fine.

An error message like "AGGREGATION EXPRESSIONS REQUIRED BY GROUP BY CLAUSE" is appeared.

[#8]

I've followed your suggestion using

"IF(Previous(Causal)=Causal and Previous(Date)=Date and Previous(Item) = Item, peek('Cumulative_Qty') + Qty, Qty) as Cumulative_Qty" without GROUP BY and the script works fine but the Cumulative_Qty field is empty.

which is the other method?

[Oleg_Troyansky]

That's exactly my point (read above) - you can use either GROUP BY with sum(), or Peek and Previous, but without Group By.

The GROUP BY clause requires using aggregation functions like SUM().

In order to follow the technique that I suggested, remove the GROUP BY clause.

[#10]

I'm sorry but there isn't an accumulation with this method.