Dynamic Line Chart

[Mixedup]

I have a KPI which shows avg days of availability of placement data. I have to plot this KPI against a dimension "month-year" which provides value in form MMM-YY. But, if plot avg days of availability of placement data against month year in line graph, for an example for April 2025 and has value for April 2025 as 7, we don't want the line to show that point as 7. Instead we want to depict a Rolling 12 months period, in this case it is May 2024 to April 2025, so the dot of April 2025 on line graph should show combined value of this R12 period. Similarly for March 2025, it should not be just March 2025 data, instead it should be April 2024 to March 2025, on the March 2025 point. We want to obtain a line graph of the same where each point on line shows cumulative data of previous 12 months from that particular month.

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[nevopotokcloudinary]

Hi,

You can use the "Moving Averages" option in Line chart and customize it to 12 steps

hope it's help

[stevedark]

Hi @Mixedup 

Take a look at this blog post, here:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/

It's old, but the functionality will still work.

For each Month in your data model you will want 12 rows in a dimension called Rolling 12 Month. You can do this with a RESIDENT load and a WHILE statement in your load script:

Something like:

Rolling12:
LOAD DISTINCT
   [Month-Year] as [Rolling 12 Month],
   Date(AddMonths([Month-Year], IterNo()*-1), 'MMM-YYYY') as [Month-Year]
   WHILE IterNo() <= 11
   ;
LOAD DISTINCT
    [Month-Year]
RESIDENT YourData;

Once you have this you should just be able to use the rolling 12 dimension in your chart.

This has the advantage over accumulation options in charts that it still gives a rolling 12 value even if you make a selection on the Rolling 12 dimension.

This will introduce some new Month-Year values, as the first month 12 month rolling value will accumulate 11 months that are not there. That can be resolved with a preceding load an a WHERE statement, if you want to tidy that up.

Hope that helps.

Steve