Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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)
Thanks for your help,
But how can I sort by that and group by STCK in this case?
Thanks
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.
Then you would need to add set analysis for your year into the FirstSortedValue expression.
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;
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!