14 Replies Latest reply: Jun 1, 2017 10:36 AM by Oleg Troyansky RSS

    Cumulative sum in script

    Michele Pierobon

      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.

        • Re: Cumulative sum in script
          Vineeth Pujari

          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;

          • Re: Cumulative sum in script
            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

              • Re: Cumulative sum in script
                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

                • Re: Cumulative sum in script
                  Michele Pierobon

                  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?

                • Re: Cumulative sum in script
                  Andrey Khoronenko

                  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

                    • Re: Cumulative sum in script
                      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.

                      • Re: Cumulative sum in script
                        Michele Pierobon

                        Unfortunately it doesn't work fine.

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

                         

                          • Re: Cumulative sum in script
                            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.

                              • Re: Cumulative sum in script
                                Michele Pierobon

                                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.

                                  • Re: Cumulative sum in script
                                    Oleg Troyansky

                                    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

                                • Re: Cumulative sum in script
                                  Andrey Khoronenko

                                  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