3 years cumulative return in Line chart QlikView

[EliAttiaQlik]

Hi folks,

I try to calculate cumulative return and display the information in a Line chart.

Axis X is months and Axis Y is yields.

For each month on X-axis I have to present a return of 3 years.

That is, in month 01/2021 on the graph, I have to present a cumulative return from month 01/2018 to month 01/2021, in month 02/2021 on the graph, I have to present a cumulative return from month 02/2018 to month 02/2021 and so on.

Anyone have an idea what the expression is?

[edwin]

i would solve this by creating abridge between your calendar and the fact table.  make an association between the month in your calendar to the month in your fact where the fact month >=your month-3 years up to the month

where factMonth>=addmonths(month,-3*12) and factMonth<=month;

this way when you select 1/2021 you effectively select 1/2018 to 1/2021 and so on per requirement.

Fact:
load date as factDate, monthstart(date) as factMonth, amount;
load date(today() - floor(rand()*4*365)) as date, floor(rand()*100) as amount
while iterno()<=4*365;
load 1 AutoGenerate(1);

NoConcatenate
tmpBridge: load distinct factMonth Resident Fact;

inner join (tmpBridge) load factMonth as month Resident tmpBridge;


NoConcatenate Bridge: load factMonth, month 
resident tmpBridge
where factMonth>=addmonths(month,-3*12) and factMonth<=month;

drop table tmpBridge;

[edwin]

in your chart, you use month (from your calendar) as dimension and sum(amount) as expression and each expression will be total of prior 3 years + current month

[edwin]

im sure there is a way to do this without a bridge and use a smart expression in the front end.  but the advantage of this approach is that you move the burden of processing the aggregation to the script and not the front end.  this will prove beneficial once you get past 10s of millions of records.  

[edwin]

there will be some nuances of course if not all months are represented in your fact table.  but the idea of the solution will be the same you just need to tweak this part:

NoConcatenate
tmpBridge: load distinct factMonth Resident Fact;

[EliAttiaQlik]

hi, thanks for your reply

The 3 years is just an example. the user can select 3 years or any other range of months. That is why the solution should be an expression in the front end. 
The amount of records is not an issue here, we are talking about thousands of securities and 10 years of history.  
the data stored in only one table that contains security number and a numeric field represents the yield, so in my opinion, it must be a straight forward solution.

Can someone help with out-of-the-box solution?

Thanks

[EliAttiaQlik]

10x Edwin, I will use this approach for intermediate while i will keep searching a wide front end solution

[edwin]

just in case you need it, you can create multiple date types in the bridge, then just add the date type in your set analysis.  of course there should be a finite set of date range selections the user can choose so each of those selections will have a corresponding date type

 good luck