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

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

[brunolelli87]

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!

[brunolelli87]

Your solution worked perfectly!

Thank you

[Saravanan_Desingh]

you welcome