Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys,
I have an excel spreadsheet with the following format:
Ticker | Periodo | Ativo Total | Ativo Circulante | Disponibilidades |
ITUB4 | 31/12/2019 | 182347169,8 | 26470174,72 | 11905 |
ITUB4 | 30/09/2019 | 171856281,6 | 21212354,56 | 51645 |
ITUB4 | 30/06/2019 | 175201058,8 | 16937673,73 | 18392 |
ITUB4 | 31/03/2019 | 180858191,9 | 20059187,2 | 58415 |
ITUB4 | 31/12/2018 | 198186549,2 | 18694776,83 | 3385456,896 |
ITUB4 | 30/09/2018 | 196365320,2 | 12278881,28 | 665008 |
ITUB4 | 30/06/2018 | 192919715,8 | 12151491,58 | 636017,984 |
ITUB4 | 31/03/2018 | 185301794,8 | 16104360,96 | 597940,992 |
ITUB4 | 31/12/2017 | 192388071,4 | 28621142,02 | 625811,008 |
BBDC4 | 31/12/2019 | 182347169,8 | 26470174,72 | 11905 |
BBDC4 | 30/09/2019 | 171856281,6 | 21212354,56 | 51645 |
BBDC4 | 30/06/2019 | 175201058,8 | 16937673,73 | 18392 |
BBDC4 | 31/03/2019 | 180858191,9 | 20059187,2 | 58415 |
BBDC4 | 31/12/2018 | 198186549,2 | 18694776,83 | 3385456,896 |
BBDC4 | 30/09/2018 | 196365320,2 | 12278881,28 | 665008 |
BBDC4 | 30/06/2018 | 192919715,8 | 12151491,58 | 636017,984 |
BBDC4 | 31/03/2018 | 185301794,8 | 16104360,96 | 597940,992 |
BBDC4 | 31/12/2017 | 192388071,4 | 28621142,02 | 625811,008 |
How can I SUM, the values of the column DISPONIBILIDADES of the selected Ticker (ITUB4 for example), that belongs to:
1. The Last available year? (In this case the system should check for all available data for the last year, on column Periodo)
2. The Last available quarter? (In this case, the system should check the most current Periodo available)
How can I do that?
Thanks
I've created a prototype for you. Please note that you do need a calendar containing a quarter field.
See attached qvw
if i understood correctly maye be
=Sum({<Ticker={'ITUB4'},Periodo={"$(=Date(Max(Periodo)))"}>} Disponibilidades)
Output:
Data:
LOAD Ticker,Date(Periodo) as Periodo, [Ativo Total], [Ativo Circulante], Disponibilidades INLINE [
Ticker, Periodo, Ativo Total, Ativo Circulante, Disponibilidades
ITUB4, 31/12/2019, "182347169,8", "26470174,72",11905
ITUB4, 30/09/2019, "171856281,6", "21212354,56", 51645
ITUB4, 30/06/2019, "175201058,8", "16937673,73", 18392
ITUB4, 31/03/2019, "180858191,9", "20059187,2", 58415
ITUB4, 31/12/2018, "198186549,2", "18694776,83", "3385456,896"
ITUB4, 30/09/2018, "196365320,2", "12278881,28", 665008
ITUB4, 30/06/2018, "192919715,8", "12151491,58", "636017,984"
ITUB4, 31/03/2018, "185301794,8", "16104360,96", "597940,992"
ITUB4, 31/12/2017, "192388071,4", "28621142,02", "625811,008"
BBDC4, 31/12/2019, "182347169,8", "26470174,72", 11905
BBDC4, 30/09/2019, "171856281,6", "21212354,56", 51645
BBDC4, 30/06/2019, "175201058,8", "16937673,73", 18392
BBDC4, 31/03/2019, "180858191,9", "20059187,2", 58415
BBDC4, 31/12/2018, "198186549,2", "18694776,83", "3385456,896"
BBDC4, 30/09/2018, "196365320,2", "12278881,28", 665008
BBDC4, 30/06/2018, "192919715,8", "12151491,58", "636017,984"
BBDC4, 31/03/2018, "185301794,8", "16104360,96", "597940,992"
BBDC4, 31/12/2017, "192388071,4", "28621142,02", "625811,008"
];
Last available Year
=sum({<[Periodo]={">=$(=YearStart(max([Periodo])))<=$(=YearEnd(max([Periodo])))"}>}[Disponibilidades])
Last available Quarter
=sum({<[Periodo]={">=$(=QuarterStart(max([Periodo])))<=$(=QuarterEnd(max([Periodo])))"}>}[Disponibilidades])
See attached qvw.
Hello,
Thanks for your quick response, but it's not working...
What's not working? Please explain. Are we not addressing the correct issue or are you having trouble implementing the suggested solution into your application?
Thanks for all your support Vegar!
The formula you provided is calculating just the Last Year and the Last Quarter, not considering what day is today.
Once I'm working with a chart where Date is a Dimension, I have to consider that the "Todays's date" is the date of each specific bar on my chart. And based on this "Today's date" I would like to get the Revenue from the Last Year, and Last Quarter, based on that date.
Is it more clear now?
Thanks
I think I understand what you are trying to do. Are you trying to create a bar chart with Rolling 3 an Rolling 12 months values? You can solve this by two approaches.
In some chart types you can choose to accumulate an expression x no of steps backward. See picture below.
This will probably solve you issue.
IF not then you could consider adjusting your data model with an AsOfTable for your periodo. I would recommend you to look into The As-of Table blogpost by HIC. It will make you very flexible in relation to create aggregated values in you application.
Below you see that the two apporaches delivers the same output.
Im attaching a qvw for your reference.
Hello,
Thanks again for your very nice post!
But it's not what I'm looking for... please, chekc the picure below for a better understanding.
As you can see here, I already have the red bars (Lucro) and the blue line (Cotação), and I want to create this new purple line, that is expressed as Cotação / Lucro.
As you can see, I have information for Cotação on a daily basis, but I don't have the information about Lucro on a dayly basis... So, to calculate my purple line, I have to use the current value of Cotação, and the last available information of Lucro (considering the day you are on a chart).
That's why I can not use the lower red bar to calculate my green start equation. To calculate the green start equation I have to use the big red bar, and to calculate the pink circle I have to use the lower red bar.
Is it better now?
Thanks!!!
Does anybody know how to do it?
I've created a prototype for you. Please note that you do need a calendar containing a quarter field.
See attached qvw