3 Replies Latest reply on Nov 13, 2018 3:17 PM by Don Wise

# Previous Dynamic Average Calc With Parameter

Hi All,

I am trying to calculate Average of dynamic months entered as Parameter. I am trying with LOD to calculate Average but not not getting it right.

What I am trying to do is,

1. In sheet Quantity-2 , We see the months 5/1/2017-7/1/2017 because I have selected the Date as 8/31/2017 and and entered the Parameter value as 3.

2. So, Past 3 months is displayed for 8/31/2017. Average sum(quantity) for three months is 287.33 .

3. I am using below calc to fix the average value for the past months for any selected Date range.

{ FIXED :

AVG({ FIXED : SUM( IF  [Date slice Filter] >= Dateadd( [Select Period], -[Enter The Period To Cacl Avg], DATETRUNC([Select Period],[Date Selection]))

and [Date slice Filter]< dateadd( [Select Period],-1,[Date Selection]) then

[Quantity] end) })}

4. With this calculation , I am getting average as 862 and not 287.33.

What am I missing here?

• ###### 1. Re: Previous Dynamic Average Calc With Parameter

Hello Susheela,

AVG({ INCLUDE : SUM( IF  [Date slice Filter] >= Dateadd( [Select Period], -[Enter The Period To Cacl Avg], DATETRUNC([Select Period],[Date Selection]))

and [Date slice Filter]< dateadd( [Select Period],-1,[Date Selection]) then

[Quantity] end) })

Thx, Don

• ###### 2. Re: Previous Dynamic Average Calc With Parameter

Hi Don,

thank you. Yes include works when I have the Average Filter .

But I want to show Actual vrs average in the same sheet (I am referring to the sheet With the filter).

When I try to bring this Calculation in this sheet, the calc is breaking.

any Thoughts?

• ###### 3. Re: Previous Dynamic Average Calc With Parameter

Hello Susheela,

If you're stating that the pill turns red, yes, that would be expected behavior in changing that calculation to the one I noted. Simply drag it off and then drag the newly changed and now AGGREGATED calc back on; the revision in the calculation changes it from an AVG to AGG.

Please see attached 10.5 workbook and below screenshot: