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

# Calculate Difference Quantity based on Previous Date

Hello Qlik community,

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.

 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

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".

 Date Material Quantity Final Quantity Max Quantity 12/12/2016 10004 280 350 280 11/11/2016 10004 60 350 60 10/10/2016 10004 700 350 10

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

Hi Ibrahim,

If I understand the condition, it may be

Table0:

[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

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

Regards,

Andrey

• ###### Re: Calculate Difference Quantity based on Previous Date

ahaahaaha Hi Andrey,

thank you very much for your response.

Left Join

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;

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

Hi Ibrahim,

It is necessary to load the table with the selection condition  like this

Resident Table0

Where [Max Quantity] <> [Final Quantity];

Regards,

Andrey

• ###### Re: Calculate Difference Quantity based on Previous Date

ahaahaaha Hi Andrey,

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

My code:

Table0:

[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

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

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

Resident Table1

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

Drop Table Table1;

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

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

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

Hi jayanttibhe,

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:

[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

Hi Ibrahim,

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

Table0:

[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:

Resident Table0

Order By Date desc;

//further all as above

Left Join

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