
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to total over periods
Hi experts
We have this formula to calculate the sales over the last 12 rolling months:
Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}Sales)
The table looks like below. I need to adjust the formula to get the sum over these months for a KPI chart. The KPI should show the result of 29.55
I tried with sum ( total <YearMonth> Sales) but it didn't work. Any ideas how to solve this issue? Thanks!
- Subscribe by Topic:
-
Chart
-
Client Managed
-
Developers
-
expression
-
General Question
-
Set Analysis
-
Visualization

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you tried to put your initial expression in the KPI object?
Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}Sales)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
as KPI it shows a value incorrect, expected value is 29.55.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you asked QS to accumulate values in your tables?
If this is the case you can try this:
Sum(Aggr(Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}Sales),[YearMonth]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it gives the same result of 2.51

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So you are accumulating in your table?
And do you have an active selection on YearMonth?
If yes, try this:
Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>} Aggr(Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}Sales),[YearMonth]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this for the KPI:
Sum({<[YearMonth]={"$(=MonthEnd(Max(YearMonth)))"}>}Sales)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@TomBond77 Please use the below script in your Load Editor:
NoConcatenate
Temp:
Load Left(YearMonth,4) as Year,
Date(Date#(Right(YearMonth,3),'MMM'),'MM') as Month,
Date(Date#(Left(YearMonth,4)&'/'& Date(Date#(Right(YearMonth,3),'MMM'),'MM')&'/'&'01','YYYY/MM/DD'),'MM/DD/YYYY')
as Date1,
Sales
Inline [
YearMonth, Sales
2022-Nov,2.52
2022-Dec,6.82
2023-Jan,9.66
2023-Feb,11.68
2023-Mar,14.27
2023-Apr,16.31
2023-May,18.85
2023-Jun,21.02
2023-Jul,23.12
2023-Aug,25.50
2023-Sep,27.77
2023-Oct,29.55
];
Exit Script;
In KPI use the below expression:
Sum({<Date1={"$(=Max(Date1))"}>}Sales)
If this resolves your issue, please like and accept it as a solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I’m in the same boat as you. Thinking the original formula should work in the kpi as well based on what has been provided. However, I’m wondering why the “<=” and beyond part of the set expression is needed. If we are truly looking for the rolling 12 months, shouldn’t we be able to use:
Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)))"}>} Sales)
this should sum the last 11 months from whatever the max date is in the selected dataset with the current months data. Right?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, but not yet solved 😞
Finally I have this formula:
(100
/
Sum(
{<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) )"}>}
Aggr(Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}
AlltimeIsActive),[YearMonth]))
)
*
Sum(
{<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}
Aggr(Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}
AlltimeLeaver),[YearMonth]))
It still gives me 2.51 as total:
Any further proposals to solve this issue? I would like to have the sum over all the months, around 29.
Thanks
