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

[Taoufiq_Zarra]

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"
];

[Vegar]

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.

[brunolelli87]

Hello,

Thanks for your quick response, but it's not working...

[Vegar]

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?

[brunolelli87]

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

[Vegar]

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.

[brunolelli87]

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

[brunolelli87]

Does anybody know how to do it?

[Vegar · Accepted solution]

I've created a prototype for you. Please note that you do need a calendar containing a quarter field. 

Expression: sum(Cotacao)/aggr(nodistinct sum(Lurco), Quarter)

See attached qvw