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
Thanks for your help and patience!
It's a very nice approach... but it's not working perfectly.
As you can see on the picture below, the system is considering the Lucro from 31/12/2019 on the previous dates, (21/12, 22/12, 13/12... up to 31/12). And from 02/01/2020 up to 21/01/2020 there is n value.
I'm expecting to get the values as I draw using the red lines and the blue lines.
For example:
The value of Lucro per Quarter on 24/12/2019 shouw not come from the Lucro of 31/12/2019, but from the lucro from the month 9.... And the Lucro per Quarter on 14/01/2020 that is a blank value now, should be 2.802.388, obtained from the Lucro of 31/12/2019.
How can I solve it?
Thanks
Bruno Lelli
If you add a day to the date of the lurco transaction then it will fall into t he correct Quarter.
Good!
How could I now sum the value of [Lucro] of the last 4 quarters, and show it in the same way we were dowing with the new column you teached me?
I'm not sure if I completely understand your question.
Doesn't the Lucro expression in my application meet your need? What if you put an outer sum() around it like this?
=sum(aggr(nodistinct sum(Lurco), Quarter))
Hello Vegar,
Thanks for all your assistance...
You solve my problem... your approach was amazying, and your solution was easier and faster than I was expecting.
Thank you so much!
Bye