topic Rolling 12 M Average - Chart Problem in App Development

Rolling 12 M Average - Chart Problem

basildur — Sat, 16 Nov 2024 06:32:41 GMT

Hello Experts,

First of all let me say, that I am well ware of how many times this question has been asked, but it is always slightly different for different developers.

I would like only last 12 Periods to be visible in my Line Chart, however want the expression to be calculated the same way it is.

As an example in the image below Mar-18 to Feb-19 and their respective 12 M Rolling Avg is what I want to be visible in the chart, however for the expression for Mar-18 to work properly it needs to look back as far as Mar-17 (which I don't want to show in the Chart)

Currently my expression looks like this:

RangeAvg(Above( Avg( {<[Code Group] = {'Sale'}, [grpcode Group] = {'3rd Party'}>} [Sales] ), 0, 12)) * Avg(1)

And my Dimension is Period, which is basically MonthName(DateField).

So my question is, can we limit the visibility to just last 12 Periods and keeping the first visible period calculation intact ?

Thank You in Advnace,

Vlad

Re: Rolling 12 M Average - Chart Problem

JustinDallas — Wed, 20 Feb 2019 15:49:58 GMT

You may want to consider making some dummy data and putting it into an Excel file to attach to this question. I don't mind fiddling around with Set Analysis to help someone get the answer they need, but if I have to create dummy data myself, it may not fully represent the system you are trying to solve.

Re: Rolling 12 M Average - Chart Problem

Keitaru — Fri, 22 Feb 2019 06:44:11 GMT

Did something similar to this before with reference line
Dimension
=Aggr(Only({<Month,Date={">=$(=addmonths(today(1),-12))<=$(=today(1))"}>}Month-Year),Month-Year)

Add-ons under Properties panel -> under Reference lines you can try adding you average count there.
RangeAvg(Above( Avg( {<[Code Group] = {'Sale'}, [grpcode Group] = {'3rd Party'}>} [Sales] ), 0, 12)) * Avg(1)