How to total over periods

[TomBond77]

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!

[vincent_ardiet_]

Have you tried to put your initial expression in the KPI object?
Sum({<[YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(YearMonth),-11)) & '<=' & MonthEnd(Max(YearMonth)))"}>}Sales)

[TomBond77]

as KPI it shows a value incorrect, expected value is 29.55.

[vincent_ardiet_]

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]))

[TomBond77]

it gives the same result of 2.51

[vincent_ardiet_]

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]))

[jochem_zw]

Try this for the KPI:

Sum({<[YearMonth]={"$(=MonthEnd(Max(YearMonth)))"}>}Sales)

[sidhiq91]

@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.

[Jebrezov]

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?

[TomBond77]

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