3-month-rolling-average alert

[SebastianRichter]

Dear experts,

I have a task that is to notify dashboard users once the count of cases per material within one month exceeds the 3 month rolling average.

I have material number, year-month and the count of reports per material number within this year-month.

Once the count of cases per material in one month exceed the 3 month rolling average, i would like to highlight it red.

Example as following:

What I need is the code for column 4 which shows the calculated 3 month rolling average per material number. 

I helped myself with a script for column 4 that works but makes performance of this sheet very slow and is surely not a proper way to do this:

Appreciate your help. Thanks in advance.

Best Regards

Sebastian

[WaltShpuntoff]

If you are using the autocalendar on your date field, you can use the dateField.MonthsAgo field in a set expression.

Avg({<dateField.MonthsAgo>={1,2,3}>} cases)

If you are not using the AutoCalendar, you can construct just the MonthsAgo field like this:

Let vCurrentMonth = Year(Today(1))*12 + Month(Today(1));

When you load your data, take the date and add this line as part of the load.

$(vCurrentMonth) - (Year(yourDate)*12+Month(yourDate) as MonthsAgo,

Sequencing dates will help you with any kind of rolling average without heavy chart calculations

hth

-ws

[SebastianRichter]

Thanks a lot for your feedback. Im using autocalendar but the set expression shows an error:

Can you please help me on how to resolve this error? Thanks.

[WaltShpuntoff]

I can't read your screen shot. Try cropping it to show your expression and a separate one for the error message at the bottom.

In the meantime, abandon auto-calendar and calculate your own values as outlined before.

I suspect that since auto-calendar is all calculated fields this approach should work.

-ws

[SebastianRichter]

My apologies for the bad screeshot.

Here is the code:  Avg({<Erfassungsdatum_ZQMPA.aC.MonthsAgo>={1,2,3}>} Fehlermeldung_ZQMPA)

The error: Error in set modifier expression