Calculating the difference in values between non sequential dates

[DJHodgson]

Hi All,

Many thanks in advance, I have an issue with a calculation I'm trying to figure out please.

I have 3 columns of data so for example:

Date	Product	Volume
01/01/2022	Apple	597
02/01/2022	Apple	602
03/01/2022	Apple	610
04/01/2022	Apple	580
05/01/2022	Apple	640
01/01/2022	Pear	1521
03/01/2022	Pear	1567
10/01/2022	Pear	1480
15/01/2022	Pear	1450
20/01/2022	Pear	1598
01/01/2022	Banana	265
02/01/2022	Banana	312
03/01/2022	Banana	105
18/01/2022	Banana	420
19/01/2022	Banana	176

So what I am trying to do is to get the differences plus or minus based on the above to give a result like the below:-

Date	Product	Volume	Difference
01/01/2022	Apple	597
02/01/2022	Apple	602	5
03/01/2022	Apple	610	8
04/01/2022	Apple	580	-30
05/01/2022	Apple	640	60
01/01/2022	Pear	1521
03/01/2022	Pear	1567	46
10/01/2022	Pear	1480	-87
15/01/2022	Pear	1450	-30
20/01/2022	Pear	1598	148
01/01/2022	Banana	265
02/01/2022	Banana	312	47
03/01/2022	Banana	105	-207
18/01/2022	Banana	420	315
19/01/2022	Banana	176	-244

The problem I have is that the dates are not sequential so I'm struggling on how to do this in qlik, any help would be appreciated, I don't mind if its easier to do it in the script or as a set analysis its whatever works to be honest.

Many Thanks in advance

[MarcoWedel]

Hi,

maybe like this:

table1:
LOAD Date, 
     Product, 
     Volume,
     If(Product=Previous(Product),Volume-Previous(Volume)) as Difference
FROM [https://community.qlik.com/t5/App-Development/Calculating-the-difference-in-values-between-non-sequential/td-p/1884665] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

Marco

[chris_djih · Accepted solution]

Do you want this achieve in the script or in a chart?
if in a chart try Above() And use the sorting tab to make it a the correct sequential ordering.
if in Script use the order by key-word to bring oyur table in the correct order and stick to the script from Marco.

[DJHodgson]

Hi,

Many thanks for this, the Above() works in the table which was exactly what I wanted thank you.