Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EliAttiaQlik
Contributor II
Contributor II

3 years cumulative return in Line chart QlikView

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?

7 Replies
edwin
Master II
Master II

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
Master II
Master II

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_0-1613914857201.png

 

edwin
Master II
Master II

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
Master II
Master II

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

edwin
Master II
Master II

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