Calculate Difference Quantity based on Previous Date

[#1]

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.

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

[ahaahaaha · Accepted solution]

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

Regards,

Andrey

[#3]

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;

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

[#4]

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;

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

[jayanttibhe]

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

[#6]

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

[#7]

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

[ahaahaaha]

Hi Ibrahim,

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

LOAD*

Resident Table0

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

Regards,

Andrey

[ahaahaaha]

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