Rolling 12 months vs previous 12 months not working correctly in line chart

[Dat]

Here’s a cleaned-up and more natural version of your message:

---

Hello,

I’m trying to build a line chart in Qlik Cloud to show a rolling 12-month evolution compared to the previous 12 months, but I’m running into issues with both the calculation and the display.

For each month, I want to calculate:

• A rolling 12-month sum (e.g. April N-1 → March N)

• Compared to the previous 12 months (April N-2 → March N-1)

• Displayed as a percentage variation

Example:
March 2026 = Sum(April 2025 → March 2026)
Compared to = Sum(April 2024 → March 2025)

I’m using a monthly date field as the dimension.

The following measure gives me the expected result, but only for a single point in time (based on Max(Date)). I would like to get the same logic applied dynamically for each month in a line chart:

Sum({<[Année exercice]=, [Mois exercice]=, [Date de référence]=, Période=,
Date = {"$(= '>=' & Date(MonthStart(AddMonths(Max(Date), -11)), 'DD/MM/YYYY') 
& '<=' & Date(MonthStart(Max(Date)), 'DD/MM/YYYY'))"}>} [Montant CA TTC])
/
Sum({<[Année exercice]=, [Mois exercice]=, [Date de référence]=, Période=,
Date = {"$(= '>=' & Date(MonthStart(AddMonths(Max(Date), -23)), 'DD/MM/YYYY') 
& '<=' & Date(MonthStart(AddMonths(Max(Date), -12)), 'DD/MM/YYYY'))"}>} [Montant CA TTC])
- 1

I also tried using RangeSum() and Above(). It partially works, but:

• I can’t make it fully dynamic per month

• I can’t restrict it to the last 12 months properly

• It either shows the full history or nothing at all when I filter on a specific date/month

(
    RangeSum(
        Above(
            TOTAL Sum([Montant CA TTC]),
            0,
            12
        )
    )
    /
    RangeSum(
        Above(
            TOTAL Sum([Montant CA TTC]),
            12,
            12
        )
    )
) - 1

Would you have a recommendation on how to correctly implement a rolling 12-month vs previous 12-month calculation that works dynamically for each point in a line chart?

Thanks in advance!

[marcus_sommer]

I don't want to say that's not possible to create such view with an aggr() construct and an interrecord-logic + n conditions but it's rather complex. Therefore I wouldn't go such way else creating an appropriate dimensional structure within the data-model and using it as chart-dimension.

The main-idea is beautifully explained here: The As-Of Table - Qlik Community - 1466130.

[Daniel_Castella]

Hi @Dat 

You need to link the Previous Year data with the current MonthYear in the backend. Otherwise, you will not able to do the comparison in front end. Also, the set analysis is not correct.

Check this script example:

Main:
LOAD Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,Sales
INLINE [
Date,	Sales
30/04/2025,		1
29/05/2025,		2
30/06/2025,		3
29/07/2025,		4
31/08/2025,		5
29/09/2025,		6
30/10/2025,		7
21/11/2025,		8
29/12/2025,		9
01/10/2025,		10
09/04/2025,		11
03/03/2025,		12
07/02/2025,		13
30/01/2025,		14
12/01/2026,		1
21/02/2026,		2
15/03/2026,		3
20/04/2026,		4
19/05/2026,		5
22/06/2026,		6
18/08/2026,		7
21/07/2026,		8
14/09/2026,		9
20/10/2026,		10
17/11/2026,		11
27/12/2026,		12
16/11/2026,		13
26/01/2026,		14
12/01/2024,		1
21/02/2024,		2
15/03/2024,		3
20/04/2024,		4
19/05/2024,		5
22/06/2024,		6
18/08/2024,		7
21/07/2024,		8
14/09/2024,		9
20/10/2024,		10
17/11/2024,		11
27/12/2024,		12
16/11/2024,		13
26/01/2024,		14
];

Main2:
NoConcatenate
Load *,Year(Date)&num(month(Date),'00') as YearMonth, 'CY' as Flag
Resident Main;

Concatenate(Main2)
Load *,Year(AddYears(Date,1))&num(month(AddYears(Date,1)),'00') as YearMonth, 'PY' as Flag
Resident Main;

Drop Table Main;

And a line chart with the following set analysis:

Sum({<YearMonth=,Flag={'CY'},Date = {"<=$(=Date(MonthStart(Max(Date)), 'DD/MM/YYYY')) >=$(=Date(MonthStart(AddMonths(Max(Date), -11)), 'DD/MM/YYYY'))"}>}Sales) 
/
Sum({<YearMonth=,Flag={'PY'},Date = {"<=$(=Date(MonthStart(AddMonths(Max(Date),-12)), 'DD/MM/YYYY')) >=$(=Date(MonthStart(AddMonths(Max(Date), -23)), 'DD/MM/YYYY'))"}>}Sales) 
-1

In my end it works well, you just need to adapt to yours. Let me know if it works for you.

Kind Regards

Daniel

[brunobertels]

Hi 

May be with the second expression using rangesum and above , you should combine it with agrr() 

sum( aggr( rangesum( above(  TOTAL sum([Montant CA TTC]),0,12) ),Month)) 

// where Month dimension is your month dimension corresponding to your Date Field //

/ 

sum( aggr( rangesum( above(  TOTAL sum([Montant CA TTC]),12,12) ),Month)) 

OR : 

sum(aggr( 

rangesum( above(  TOTAL sum([Montant CA TTC]),0,12) )

/

rangesum( above(  TOTAL sum([Montant CA TTC]),12,12) )

,Month))

You may need also to use a calculated dimension for Month to restrict it to last 12 month 

Ressource & link usefull 

Calculating rolling n-period totals, averages or other aggregations

https://community.qlik.com/t5/Design/Accumulative-Sums/ba-p/1468010

Hope it helps