8 Replies Latest reply: Feb 2, 2017 3:18 AM by Andrey Khoronenko RSS

    Calculate Difference Quantity based on Previous Date

    Ibrahim Düzenli

      Hello Qlik community,

       

      first of all thank you in advance for your help.

       

      Hopefully, you have an idea and solution for my problem.

       

      My Table has four columns, which are sorted by Date (Newest to Oldest). The Table contains different Material numbers, but for each Material number the "Final Quantity" is always the same. All quantities are always positive numbers.

       

      DateMaterialQuantityFinal Quantity
      12/12/201610004280350
      11/11/20161000460350
      10/10/201610004700350
      10/3/201610004100350

       

      I would like to create a new table with a new column "Max Quantity".

       

      The new column should be contain the same information like column "Quantity", but

       

      IF the SUM of the previous "Quantity" [based on Date] is reached, then

      calculate a Difference between "Final Quantity" and Sum of the previous "Quantity".

       

       

      DateMaterialQuantityFinal QuantityMax Quantity
      12/12/201610004280350280
      11/11/2016100046035060
      10/10/20161000470035010

       

      In this example the the column Quantity has on Date 10/10/2016 more then 350 quantities, so the Max Quantity is only the difference 10.

       

      I am working on this problem since a few days and I searched for solutions in Qlik community, but i didn't find one.

      Hopefully, you have an idea.

       

      Best regards,

      Ibr

        • Re: Calculate Difference Quantity based on Previous Date
          Andrey Khoronenko

          Hi Ibrahim,

           

          If I understand the condition, it may be

           

          Table0:

          LOAD * Inline

          [Date, Material, Quantity, Final Quantity

          12.12.2016, 10004, 280, 350

          11.11.2016, 10004, 60, 350

          10.10.2016, 10004, 700, 350

          10.3.2016, 10004, 100, 350];

           

           

          Left Join

          LOAD*,

          Previous(RangeSum(Quantity, Peek('Bsum'))) as Bsum, //accumulating a total of Quantity in Bsum with offset by one line

          If(Quantity > [Final Quantity], [Final Quantity] - Previous(RangeSum(Quantity, Peek('Bsum'))), Quantity) as [Max Quantity] //We check the condition

          Resident Table0;

           

          Result

           

          1.jpg

          Regards,

          Andrey

            • Re: Calculate Difference Quantity based on Previous Date
              Ibrahim Düzenli

              ahaahaaha Hi Andrey,

               

              thank you very much for your response.

               

              I took your code and added a CumulativeQuantity, see code below.

               

              Left Join

              LOAD*,

                Previous(RangeSum(Quantity, Peek('Bsum'))) as Bsum, //accumulating a total of Quantity in Bsum with offset by one line

                If(Quantity > [Final Quantity], [Final Quantity] - Previous(RangeSum(Quantity, Peek('Bsum'))), Quantity) as [Max Quantity], //We check the condition

                  If(RowNo()=1, Quantity, If(Material = Peek(Material),Quantity + Peek(CumulativeQuantity),Quantity)) AS [CumulativeQuantity] //calculate cumulative

              Resident Table0;

              Table.png

               

              Now, I would like to delete the red line. Because the Sum of the "Max Quantity" is equal to "Final Quantity".

               

              Do you have an idea, how its possible to do it?

               

              Best regards,

              Ibr

            • Re: Calculate Difference Quantity based on Previous Date
              Ibrahim Düzenli

              ahaahaaha Hi Andrey,


              i did it with you help. Thank you very much.


              My code:


              Table0:

              LOAD * Inline

              [Date, Material, Quantity, Final Quantity

              12.12.2016, 10004, 280, 350

              11.11.2016, 10004, 60, 350

              10.10.2016, 10004, 700, 350

              10.3.2016, 10004, 100, 350];

               

              Left Join

              LOAD*,

                Previous(RangeSum(Quantity, Peek('Bsum'))) as Bsum, //accumulating a total of Quantity in Bsum with offset by one line

                If(Quantity > [Final Quantity], [Final Quantity] - Previous(RangeSum(Quantity, Peek('Bsum'))), Quantity) as [Max Quantity], //We check the condition

                  If(RowNo()=1, Quantity, If(Material = Peek(Material),Quantity + Peek(CumulativeQuantity),Quantity)) AS [CumulativeQuantity] //cumulative quantity

              Resident Table0;

               

               

              Table1:

              NoConcatenate

              Load *,

              If(RowNo()=1, Quantity, If(Material = Peek(Material),[Max Quantity] + (Previous(RangeSum([Max Quantity], Peek('Bsum')))),0)) AS [Max CumulativeQuantity]

              Resident Table0;

              Drop Table Table0;

               

               

              Table2:

              NoConcatenate

              Load *

              Resident Table1

              Where [Max CumulativeQuantity]<=[Final Quantity];

              Drop Table Table1;

              Table2.JPG

               

              I created a new column "Max CumulativeQuantity" based on "Max Quantity".

              And I added Where [Max CumulativeQuantity]<=[Final Quantity];

               

              Thank you very much for your help.

               

              Best regards,

              Ibr

                • Re: Calculate Difference Quantity based on Previous Date
                  Jayant Tibhe

                  Ibr , How you are marking your answer as correct ?  @Andrey Khoronenko  has responded and helped you. You have to mark his answer as correct answer. You should refrain to do this.

                    • Re: Calculate Difference Quantity based on Previous Date
                      Ibrahim Düzenli

                      Hi jayanttibhe,

                       

                      as I know "correct answer" means that question or problem is answered. Andrey Khoronenko helped me a lot and I wrote several times "thank you for your help".

                       

                      Have a nice day.

                       

                      Best regards,

                      Ibr

                        • Re: Calculate Difference Quantity based on Previous Date
                          Ibrahim Düzenli

                          Hi jayanttibhe,


                          Hi Andrey Khoronenko,


                          I changed the Input data from sorted by date to unsorted. And I added new material. Now, the whole code doesn't work.

                          Maybe you can help me.


                          Table0:

                          LOAD * Inline

                          [Date, Material, Quantity, Final Quantity

                          12/12/2016, 10004, 280, 350

                          11/11/2016, 10005, 60, 250

                          10/10/2016, 10004, 700, 350

                          12/13/2016, 10007, 40,300

                          12/14/2016, 10007, 40,300

                          12/29/2016, 10007, 40,300

                          12/11/2016, 10007, 40,300

                          12/1/2016, 10007, 40,300

                          12/23/2016, 10007, 40,300

                          10/3/2016, 10004, 100, 350

                          10/12/2016, 10005, 280, 250

                          12/12/2016, 10006, 440,400

                          1/11/2016, 10005, 60, 250

                          9/9/2016, 10005, 700, 250

                          12/27/2016, 10007, 40,300

                          12/23/2016, 10007, 40,300

                          10/4/2016, 10005, 100, 250

                          12/23/2016, 10007, 40,300];

                           

                          I would very much welcome any ideas and help. Thanks a lot.

                           

                          Best regards,

                          Ibr

                            • Re: Calculate Difference Quantity based on Previous Date
                              Andrey Khoronenko

                              Hi Ibrahim,

                               

                              Maybe so? The basis given above I took my code. You can make it their own changes as written above.

                               

                              Table0:

                              LOAD * Inline

                              [Date, Material, Quantity, Final Quantity

                              12.12.2016, 10004, 280, 350

                              11.11.2016, 10005, 60, 250

                              10.10.2016, 10004, 700, 350

                              13.12.2016, 10007, 40,300

                              14.12.2016, 10007, 40,300

                              29.12.2016, 10007, 40,300

                              11.12.2016, 10007, 40,300

                              01.12.2016, 10007, 40,300

                              23.12.2016, 10007, 40,300

                              3.10.2016, 10004, 100, 350

                              12.10.2016, 10005, 280, 250

                              12.12.2016, 10006, 440,400

                              11.01.2016, 10005, 60, 250

                              09.09.2016, 10005, 700, 250

                              27.12.2016, 10007, 40,300

                              23.12.2016, 10007, 40,300

                              04.10.2016, 10005, 100, 250

                              23.12.2016, 10007, 40,300];

                               

                              //overload the table, sorted by date

                              NoConcatenate

                              Table1:

                              LOAD*

                              Resident Table0

                              Order By Date desc;

                               

                              //further all as above

                              Left Join

                              LOAD*,

                              Previous(RangeSum(Quantity, Peek('Bsum'))) as Bsum, //accumulating a total of Quantity in Bsum with offset by one line

                              If(Quantity > [Final Quantity], [Final Quantity] - Previous(RangeSum(Quantity, Peek('Bsum'))), Quantity) as [Max Quantity] //We check the condition

                              Resident Table1;

                               

                               

                              DROP Table Table0;

                               

                              Regards,

                              Andrey