Sum the field until that specific date on a chart.

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

Hello guys,

Thanks for each one here! I'm learning a lot... Qlikview is an amazing software!

I have to create a chart with Dates as Dimension (X-axis of my chart), and the sum of the yearly Revenue until that date (that is shown on the X-axis of my chart) as the Y-axis of my chart.

The Dates as Dimension comes from an excel spreadsheet called Stoks.
The data available at this Stoks.xls spreadsheet is similar to:

Dates	Closing
05/05/2020	$ 12,33
04/05/2020	$ 14,55
05/05/2020	$ 17,22
04/05/2020	$ 14,22
05/05/2020	$ 13,33
04/05/2020	$ 12,98
05/05/2020	$ 15,88
And so son	And so son
01/01/2015	$ 11,88

From another excel spreadsheet I've some fields, including Period and Revenue:

Company	Period	Revenue
BBA1	31/12/2019	$ 3.251.896,32
BBA1	30/09/2019	$ 3.030.734,08
BBA1	30/06/2019	$ 2.617.694,98
BBA1	31/03/2019	$ 2.541.991,94
BBA1	31/12/2018	$ 2.480.429,57
BBA1	30/09/2018	$ 2.441.650,94
BBA1	30/06/2018	$ 2.017.875,97
BBA1	31/03/2018	$ 2.213.400,06
BBA1	31/12/2017	$ 2.194.258,43
BBA1	30/09/2017	$ 1.998.189,06
BBDC3	31/12/2019	$ 5.625.780,63
BBDC3	30/09/2019	$ 5.243.169,96
BBDC3	30/06/2019	$ 4.528.612,31
BBDC3	31/03/2019	$ 4.397.646,05
BBDC3	31/12/2018	$ 4.291.143,15
BBDC3	30/09/2018	$ 4.224.056,13
BBDC3	30/06/2018	$ 3.490.925,42
BBDC3	31/03/2018	$ 3.829.182,11
BBDC3	31/12/2017	$ 3.796.067,09
BBDC3	30/09/2017	$ 3.456.867,07

As you can see, the periods are expressed as dates, but basically they represent quarters, like 30/09/2017 is the 3rd quarter of 2017. The 31/12/2017 is the 4th quarter of 2017...

Creating a chart with Date as Dimension, the system will show all available dates on X-axis (and It's exactly what I'm looking for).

My question is:
On Y-axis, each bar should represent the sum of Revenues from the day that that bar represents, until one year before.

Check this example:
Lets suppose I got the Company BBDC3 on a Date of 05/05/2019.
The Y-axis should represent the sum of Revenues from 05/05/2018 up to 05/05/2018.

BBDC3	31/03/2019	$ 4.397.646,05
BBDC3	31/12/2018	$ 4.291.143,15
BBDC3	30/09/2018	$ 4.224.056,13
BBDC3	30/06/2018	$ 3.490.925,42

So, I would like to get the result equal to $ 16.403.770,75.

How can I do it?
I'm getting crazy with it

Re: Sum the field until that specific date on a chart.

Vegar — Wed, 06 May 2020 05:07:30 GMT

You could try to solve it by using set analysis. Try something like this.

=sum({<[Period]={">=$(=YearStart(max([Period])))<$(=YearEnd(max([Period])))"}>}[Revenue])

Re: Sum the field until that specific date on a chart.

brunolelli87 — Wed, 06 May 2020 14:16:36 GMT

Hello

Thanks for all your help, but it's not working... The red line should variate between those dates, and they are not. I'm using the formula you provided for both Revenue and Stocks. The fact is that Revenue belongs to the table where you can find the Period, and Stocks belongs to another table, where you can still find dates, but they are not called Period.

What should I do?

topic Re: Sum the field until that specific date on a chart. in QlikView

Sum the field until that specific date on a chart.

Re: Sum the field until that specific date on a chart.

Re: Sum the field until that specific date on a chart.