Variation between years

[brunolelli87]

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

[Or]

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)

[brunolelli87]

Thanks for your help,

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

Thanks

[Or]

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.

[brunolelli87]

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.

[Or]

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

[brunolelli87]

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

Thanks

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

[brunolelli87]

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!