Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello folks,
I have a bar chart (or a Straight Table) with the =Date(DVPG) as Dimension and 2 expressions, that are:
But the second expression is not working, it's just showing -
My intention with the second expression is to show the LOPP of 3 years ago (day-by-day).
Check the following example:
DVPG | LOPP | my question |
27/05/2020 | R$ 829,45 | R$ 299,28 |
26/05/2020 | R$ 388,41 | R$ 976,54 |
25/05/2020 | R$ 451,40 | R$ 910,95 |
… | … | … |
27/05/2017 | R$ 299,28 | R$ 968,37 |
26/05/2017 | R$ 976,54 | R$ 72,26 |
25/05/2017 | R$ 910,95 | R$ 732,61 |
… | … | … |
27/05/2014 | R$ 968,37 | |
26/05/2014 | R$ 72,26 | |
25/05/2014 | R$ 732,61 |
I also attached a QlikView file with some sample data.
I'm using a personal edition of QlikView so I won't be able to open any file from you!
Why I'm getting those 0, and sometimes depending on my trials I'm getting -
What am I doing wrong?
How can I show the result of LOPP of 3 years ago (day-by-day)?
Thanks
Check if this works
Check the attached
Script
// Carrega a Taxas
Indices:
LOAD
Date#(@1,'DD/MM/YYYY') as DVPG, // Data a ser considerada
Num#(@2, '#.##0,00;-#.##0,00', ',', '.') as IGPD, // Valor, em pontos, da IGP-DI
Num#(@3, '#.##0,00;-#.##0,00', ',', '.') as IDTS // Valor da taxa Selic
FROM
[https://www.lellidesp.com.br/Empresas/Taxas.csv]
(txt, utf8, no labels, delimiter is ';', msq, filters(
Remove(Row, Pos(Top, 1))
));
LOAD
Date(@1, 'DD/MM/YYYY') as DVPG, // Periodo.
Num(@26*1000) as LOPP // Lucro/Prejuízo do Período.
FROM
[https://www.lellidesp.com.br/Empresas/DR/AZUL4.csv]
(txt, utf8, no labels, delimiter is ';', msq, filters(
Remove(Row, Pos(Top, 1)),
Remove(Row, RowCnd(CellValue, 5, StrCnd(null)))
));
tmpDate:
LOAD Distinct DVPG
Resident Indices;
Concatenate (tmpDate)
LOAD Distinct DVPG
Resident AZUL4;
AsOfDate:
LOAD DISTINCT DVPG as AsOfDVPG,
Date(AddYears(DVPG, -3), 'DD/MM/YYYY') as DVPG,
'3 Year Ago' as Flag
Resident tmpDate;
Concatenate (AsOfDate)
LOAD DISTINCT DVPG as AsOfDVPG,
DVPG as DVPG,
'Current' as Flag
Resident tmpDate;
DROP Table tmpDate;
Now create a chart with
Dimension
AsOfDVPG
Expressions
=Avg({<Flag = {'Current'}>} LOPP)
=Sum({<Flag = {'Current'}>} IDTS)
=Sum({<Flag = {'Current'}>} IGPD)
=Avg({<Flag = {'3 Year Ago'}>} LOPP)
It seems to be working.
I'll try to implement it in my project and I'll let you know in a few minutes.
Thank you so much Sunny,
Bye
Hello Sunny,
Thanks for your help, but I'm so inexperienced, and I couldn't adapt it to my project.
Would you mind helping me to do it?
Please find attached my project.
I see you tried creating the table, but I couldn't find the script you used for this?
Yes, I'm sorry!
I tried, I did my best, but once I couldn't find a solution, I just removed the script!
And once I have no idea about how to do it, I thought that was no reason to send you my "bad" trials
Sorry, I am not sure I follow?