topic Re: Calculating the difference in values between non sequential dates in App Development

Calculating the difference in values between non sequential dates

DJHodgson — Mon, 24 Jan 2022 16:54:57 GMT

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

Re: Calculating the difference in values between non sequential dates

MarcoWedel — Mon, 24 Jan 2022 17:50:17 GMT

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

Re: Calculating the difference in values between non sequential dates

chris_djih — Tue, 25 Jan 2022 07:19:36 GMT

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.

Re: Calculating the difference in values between non sequential dates

DJHodgson — Fri, 28 Jan 2022 14:08:02 GMT

Hi,

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