topic Re: Finding the difference between aggregations in QlikView

Finding the difference between aggregations

sarahshong — Wed, 25 Nov 2020 16:16:04 GMT

I have the below data. What I'm looking for is a way to get the Difference column.

I have gotten the avg by cycle column by using this formula where Max Days is the latest date in each cycle.

if(day(DATE)=MaxDays,aggr(nodistinct Avg({$<LABEL={'First'}>} PRICE),CURVE,CYCLE),0)

CURVE	CYCLE	LABEL	DATE	PRICE	Avg by Cycle	Difference
Apples	1	first	1/1/2020	$ 1.00
Apples	1	first	1/2/2020	$ 2.00
Apples	1	first	1/3/2020	$ 4.00
Apples	1	first	1/4/2020	$ 2.50
Apples	1	first	1/5/2020	$ 1.50
Apples	1	first	1/6/2020	$ 4.00
Apples	1	first	1/7/2020	$ 3.20
Apples	1	first	1/8/2020	$ 2.40
Apples	1	first	1/9/2020	$ 5.10
Apples	1	first	1/10/2020	$ 1.60	$ 2.73
Apples	2	first	1/11/2020	$ 2.50
Apples	2	first	1/12/2020	$ 4.50
Apples	2	first	1/13/2020	$ 4.10
Apples	2	first	1/14/2020	$ 4.20
Apples	2	first	1/15/2020	$ 2.34
Apples	2	first	1/16/2020	$ 2.53
Apples	2	first	1/17/2020	$ 5.24
Apples	2	first	1/18/2020	$ 1.24
Apples	2	first	1/19/2020	$ 2.52
Apples	2	first	1/20/2020	$ 1.65	$ 3.08	$ 0.35
Apples	3	first	1/21/2020	$ 2.41
Apples	3	first	1/22/2020	$ 2.54
Apples	3	first	1/23/2020	$ 1.52
Apples	3	first	1/24/2020	$ 1.52
Apples	3	first	1/25/2020	$ 4.32
Apples	3	first	1/26/2020	$ 5.12
Apples	3	first	1/27/2020	$ 3.12
Apples	3	first	1/28/2020	$ 4.13
Apples	3	first	1/29/2020	$ 2.14
Apples	3	first	1/30/2020	$ 2.10
Apples	3	first	1/31/2020	$ 1.00	$ 2.72	$ (0.36)

Is there a way to get the difference column to take the difference between the average of the current cycle minus average of the previous cycle. I've tried above functions but I haven't found a way to make it work.

Re: Finding the difference between aggregations

Kushal_Chawda — Sat, 26 Sep 2020 11:04:53 GMT

@sarahshong try below expressions

// Avg. by Cycle =if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE), Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)) // Difference = if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE), Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)-aggr(NODISTINCT above(Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)),CURVE,CYCLE))

Sort the chart in below order

1) CURVE

2) CYCLE

3) DATE

Uncheck "Suppress zero-values" option from presentation tab properties

Re: Finding the difference between aggregations

sarahshong — Sun, 27 Sep 2020 01:59:03 GMT

Thank you for your reply. When I applied to my data I'm not getting what is expected. It worked for the second cycle but for the third cycle it came up null. For the first cycle it took the difference between the avg by cycle on the 10th and 31st.

It seems to be the second part of the expression that is creating this shift. but can't really figure out why. any suggestions? Could it have something to do with how my fields are sorted?

aggr(NODISTINCT above(Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)),CURVE,CYCLE))

Re: Finding the difference between aggregations

Kushal_Chawda — Sun, 27 Sep 2020 12:03:28 GMT

@sarahshong Please share more sample data as it will be hard to tell where is the gap. you have used set analysis for cycle. Based on your problem, I can see that you don't really need that set analysis. So remove that set analysis wherever it is used and try.