topic Re: Variation between years in QlikView

Variation between years

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

Dears,

How can I calculate the variation of VDAF between years?

This is how my Database looks like:
Obviously, I have VDAF for each company, every day since 2015...

DVPG	STCK	VDAF
31/05/2020	AZUL4	R$ 98,12
30/05/2020	AZUL4	R$ 26,33
29/05/2020	AZUL4	R$ 40,33
…	…	…
13/02/2019	AZUL4	R$ 58,50
12/02/2019	AZUL4	R$ 26,85
11/02/2019	AZUL4	R$ 9,24
…	…	…
08/05/2018	AZUL4	R$ 29,07
07/05/2018	AZUL4	R$ 34,45
06/05/2018	AZUL4	R$ 71,39
…	…	…
31/05/2020	GOLL4	R$ 86,19
30/05/2020	GOLL4	R$ 91,29
29/05/2020	GOLL4	R$ 10,04
…	…	…
13/02/2019	GOLL4	R$ 57,63
12/02/2019	GOLL4	R$ 85,97
11/02/2019	GOLL4	R$ 37,59
…	…	…
08/05/2018	GOLL4	R$ 72,32
07/05/2018	GOLL4	R$ 33,60
06/05/2018	GOLL4	R$ 89,95

I would like to obtain something like this:

Year (DVPG)	STCK	VV VDAF
2018	AZUL4	-59%
2019	AZUL4	533%
2020	AZUL4	143%
2018	GOLL4	-20%
2019	GOLL4	53%
2020	GOLL4	758%

How can I do that?
Thanks

Re: Variation between years

Or — Sun, 31 May 2020 22:13:17 GMT

That looks like you need to use two FirstSortedValue() functions - one to get the first value (sorted by date) and one with a negative sign to get the last value.

Something along the lines of FirstSortedValue(VDAF, DVPG)/FirstSortedValue(VDAF, -DVPG)

Re: Variation between years

brunolelli87 — Sun, 31 May 2020 22:28:39 GMT

Thanks for your help,

But how can I sort by that and group by STCK in this case?

Thanks

Re: Variation between years

Or — Sun, 31 May 2020 22:38:17 GMT

Since we haven't used a qualifier, e.g. TOTAL, this should respect the dimensions of the chart, which are year and STCK, I think. I'm not able to test it at the moment.

Re: Variation between years

brunolelli87 — Sun, 31 May 2020 22:45:58 GMT

Thanks for all your assistance,
But I would like to show this vale on a Label Text Field... as text, not a chart you know.

One label for 2015, another for 2016... up to 2020.

Re: Variation between years

Or — Sun, 31 May 2020 23:02:02 GMT

Then you would need to add set analysis for your year into the FirstSortedValue expression.

Re: Variation between years

brunolelli87 — Sun, 31 May 2020 23:12:58 GMT

I have recently started working with QlikView, and I don't understand it very well.
Would you mind to send me an example?

Thanks

Re: Variation between years

Saravanan_Desingh — Mon, 01 Jun 2020 00:11:39 GMT

One solution is.

SET DateFormat='DD/MM/YYYY'; tab1: LOAD *, Year(DVPG) As Yr ; LOAD * INLINE [ DVPG, STCK, VDAF 31/05/2020, AZUL4, 98.12 30/05/2020, AZUL4, 26.33 29/05/2020, AZUL4, 40.33 13/02/2019, AZUL4, 58.50 12/02/2019, AZUL4, 26.85 11/02/2019, AZUL4, 9.24 08/05/2018, AZUL4, 29.07 07/05/2018, AZUL4, 34.45 06/05/2018, AZUL4, 71.39 31/05/2020, GOLL4, 86.19 30/05/2020, GOLL4, 91.29 29/05/2020, GOLL4, 10.04 13/02/2019, GOLL4, 57.63 12/02/2019, GOLL4, 85.97 11/02/2019, GOLL4, 37.59 08/05/2018, GOLL4, 72.32 07/05/2018, GOLL4, 33.60 06/05/2018, GOLL4, 89.95 ]; tab2: LOAD RowNo() As RowID, Yr, STCK, Sum(VDAF) As Yr_VDAF Resident tab1 Group By STCK, Yr Order By STCK Asc, Yr Asc ; Left Join(tab2) LOAD Yr, STCK, Yr_VDAF, If(Previous(Yr)+1=Yr And Previous(STCK)=STCK, (Yr_VDAF-Peek(Yr_VDAF))/Yr_VDAF) As [VV VDAF] Resident tab2 ; Drop Table tab1;

Re: Variation between years

Saravanan_Desingh — Mon, 01 Jun 2020 00:12:45 GMT

Re: Variation between years

brunolelli87 — Mon, 01 Jun 2020 00:26:48 GMT

Hello Saran7de,

Thank you so much per your assistance, but I'm not sure if it's correct.

Basically I'm looking for the difference from the very first day , and the very last day of each year.
I don't care what's going on in the middle... I'm just looking for the first and last day of each year.

How can I do that?

Thank you
Enjoy your Sunday!

Re: Variation between years

Saravanan_Desingh — Mon, 01 Jun 2020 01:32:40 GMT

May be this?

SET DateFormat='DD/MM/YYYY'; tab1: LOAD *, Year(DVPG) As Yr ; LOAD * INLINE [ DVPG, STCK, VDAF 31/05/2020, AZUL4, 98.12 30/05/2020, AZUL4, 26.33 29/05/2020, AZUL4, 40.33 13/02/2019, AZUL4, 58.50 12/02/2019, AZUL4, 26.85 11/02/2019, AZUL4, 9.24 08/05/2018, AZUL4, 29.07 07/05/2018, AZUL4, 34.45 06/05/2018, AZUL4, 71.39 31/05/2020, GOLL4, 86.19 30/05/2020, GOLL4, 91.29 29/05/2020, GOLL4, 10.04 13/02/2019, GOLL4, 57.63 12/02/2019, GOLL4, 85.97 11/02/2019, GOLL4, 37.59 08/05/2018, GOLL4, 72.32 07/05/2018, GOLL4, 33.60 06/05/2018, GOLL4, 89.95 ]; tab2: LOAD RowNo() As RowID, Yr, STCK, FirstSortedValue(VDAF, DVPG) As VDAF1, FirstSortedValue(VDAF, -DVPG) As VDAF2 Resident tab1 Group By STCK, Yr ; tab3: LOAD Yr, STCK, (VDAF1-VDAF2)/VDAF2 As [VV VDAF],VDAF1,VDAF2 Resident tab2 ; Drop Table tab1, tab2;

Re: Variation between years

Saravanan_Desingh — Mon, 01 Jun 2020 01:34:05 GMT

Re: Variation between years

brunolelli87 — Mon, 01 Jun 2020 01:39:08 GMT

I believe it's correct now!

I'll jump to my project now and try to adapt it! I'll let you know if it's working or not in a while.

Thank you so much,
Enjoy your Sunday!

Re: Variation between years

brunolelli87 — Mon, 01 Jun 2020 04:14:30 GMT

Your solution worked perfectly!

Thank you

Re: Variation between years

Saravanan_Desingh — Mon, 01 Jun 2020 13:11:55 GMT

you welcome