Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ahaahaaha
Honored Contributor

Re: Cumulative sum in script

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

Re: Cumulative sum in script

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.

Highlighted
MVP
MVP

Re: Cumulative sum in script

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

Re: Cumulative sum in script

Well, now everything is perfect!!!!

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

I want to increase my development ability!!!

MVP
MVP

Re: Cumulative sum in script

Hi Michele,

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

QlikView Your Business

cheers,

Oleg Troyansky

Community Browser