topic Re: 3 years cumulative return in Line chart QlikView in QlikView

3 years cumulative return in Line chart QlikView

EliAttiaQlik — Sun, 21 Feb 2021 04:35:32 GMT

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?

Re: 3 years cumulative return in Line chart QlikView

edwin — Sun, 21 Feb 2021 13:39:09 GMT

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;

Re: 3 years cumulative return in Line chart QlikView

edwin — Sun, 21 Feb 2021 13:41:07 GMT

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

Re: 3 years cumulative return in Line chart QlikView

edwin — Sun, 21 Feb 2021 13:43:20 GMT

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.

Re: 3 years cumulative return in Line chart QlikView

edwin — Sun, 21 Feb 2021 13:45:33 GMT

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;

Re: 3 years cumulative return in Line chart QlikView

EliAttiaQlik — Sun, 21 Feb 2021 13:57:24 GMT

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

Re: 3 years cumulative return in Line chart QlikView

EliAttiaQlik — Sun, 21 Feb 2021 14:04:53 GMT

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

Re: 3 years cumulative return in Line chart QlikView

edwin — Sun, 21 Feb 2021 16:21:30 GMT

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