Sum by quarter

[brunolelli87]

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

[brunolelli87]

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

[Vegar]

If you add a day to the date of the lurco transaction then it will fall into t he correct Quarter. 

[brunolelli87]

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?

[Vegar]

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))

[brunolelli87]

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