topic Re: Sum by quarter in QlikView

Sum by quarter

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum by quarter

Taoufiq_Zarra — Tue, 05 May 2020 18:11:24 GMT

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

Re: Sum by quarter

Vegar — Tue, 05 May 2020 18:51:44 GMT

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.

Re: Sum by quarter

brunolelli87 — Tue, 05 May 2020 20:52:44 GMT

Hello,

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

Re: Sum by quarter

Vegar — Wed, 06 May 2020 04:32:57 GMT

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?

Re: Sum by quarter

brunolelli87 — Wed, 06 May 2020 14:50:27 GMT

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

Re: Sum by quarter

Vegar — Wed, 06 May 2020 19:53:10 GMT

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.

Re: Sum by quarter

brunolelli87 — Wed, 06 May 2020 23:20:08 GMT

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

Re: Sum by quarter

brunolelli87 — Thu, 07 May 2020 15:09:23 GMT

Does anybody know how to do it?

Re: Sum by quarter

Vegar — Thu, 07 May 2020 20:12:51 GMT

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

Re: Sum by quarter

brunolelli87 — Fri, 08 May 2020 02:54:10 GMT

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

Re: Sum by quarter

Vegar — Fri, 08 May 2020 06:15:48 GMT

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

Re: Sum by quarter

brunolelli87 — Sat, 09 May 2020 14:38:38 GMT

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?

Re: Sum by quarter

Vegar — Sat, 09 May 2020 17:32:58 GMT

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

Re: Sum by quarter

brunolelli87 — Sun, 10 May 2020 01:55:26 GMT

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