Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative sum in script

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.

14 Replies
ahaahaaha
Partner - Master
Partner - Master

First, you can create a new table with the required grouping, and then reload it, accumulating data in the field

RangeSum (QTA_MOV_1YB, Peek ( 'CUM_QTA_MOV_1YB')), как CUM_QTA_MOV_1YB

Not applicable
Author

Thank you Oleg, I'm confident that i'm near the solution but there are some problems yet.

This is my script with the peek and previous method:

WAREHOUSE_MOVEMENTS:

LOAD

    ITEM,

  

    DATE_MOV,

    MONTH_MOV,

  

    QTY_MOV,

    CAUSAL_MOV

RESIDENT MOV_MAG

ORDER BY ITEM ASC,DATE_MOV ASC,CAUSAL_MOV ASC;

DROP TABLE MOV_MAG;

CUMULATIVE_SUM:

LOAD

    ITEM,

    IF((

        Previous(CAUSAL_MOV)        =    CAUSAL_MOV     and

        Previous(DATE_MOV)             =    DATE_MOV         and

        Previous(ITEM)                       =    ITEM

        ),

            (peek('CUM_QTA_MOV_1YB')+QTY_MOV),

                QTY_MOV)                                                         AS CUM_QTA_MOV_1YB

RESIDENT WAREHOUSE_MOVEMENTS;

Unfortunately the result isn't so good yet.

and so on.

I think that the key field ITEM isn't enough to linking tables.

I show you what i want to have like result.

.................................................................................................................................... and so on.

I've changed the key with a composite key but it hasn't a good idea.

Can you tell me what there is wrong??

Thank you in advance!!! I think i will buy your book.

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Michele,

A few comments:

1. Based on your desired output, it looks like you want to accumulate quantities just by Items, not by Item, Date, and Causal.

So, in this case, you should only compare  Item with the Previous(Item).

2. Based on our desired output, you need to add the cumulative "running total" in the same table with the movements, not in a separate table by ITEM - that would produce wrong results.

So, I think your script should look like this:

WAREHOUSE_MOVEMENTS_TEMP:

LOAD

    ITEM,

 

    DATE_MOV,

    MONTH_MOV,

 

    QTY_MOV,

    CAUSAL_MOV

RESIDENT

     MOV_MAG

ORDER BY

     ITEM ASC,DATE_MOV ASC,CAUSAL_MOV ASC;

DROP TABLE MOV_MAG;

WAREHOUSE_MOVEMENTS:

LOAD

    ITEM,

    DATE_MOV,

    MONTH_MOV,

 

    QTY_MOV,

    IF((

        Previous(ITEM)                       =    ITEM

        ),

            RangeSum(peek('CUM_QTA_MOV_1YB') , QTY_MOV),

                QTY_MOV)                                                         AS CUM_QTA_MOV_1YB

RESIDENT

     WAREHOUSE_MOVEMENTS_TEMP

ORDER BY

      ITEM,

      DATE_MOV,

     CAUSAL_MOV

;

DROP TABLE WAREHOUSE_MOVEMENTS_TEMP;

I also replaced a simple addition ( the plus sign) with a RangeSum, to protect it against an accidental NULL value...

Now you better buy my book 🙂

cheers,

Oleg Troyansky

Not applicable
Author

Well, now everything is perfect!!!!

I surf in internet to buy your book of course!!

I want to increase my development ability!!!

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Michele,

glad I could help. You can find the book here:

QlikView Your Business

cheers,

Oleg Troyansky