Pivot Table - Avg last 5 days

[pamaxeed]

Hi,

I have a question regarding the following calculation.

I have a pivot table with Account and Date as dimension:

How is it possible to get the AVG Amount of the last 5 days for every single date dimension value?

I am struggling I have tried with Set Analysis but I could not come to a solution

avg({<[Ref Date]={"<=$(=Max([Ref Date]))>=$(=Only([Ref Date]-5)"}>} AMOUNT_BU)

Anyone can help me?

Update:

I have a

dded a sample application, the result in the Avg Last 5 Days is not correct!

The expression I used here is:

avg({<[Date]={'>=$(=Date(Max([Date])-5))<=$(=Date(Max([Date])))'}>} Amount)

Kind regars,

Patric

Nachricht geändert durch Patric Amatulli

[pamaxeed]

Great!

[jonas_rezende]

Hi, Patric Amatulli

Try:

AVG({<[Ref Date] = {">=$(=DATE(Max([Ref Date])-5)) <=$(=Max( Date([Ref Date])))"}>} AMOUNT_BU)

Hope this helps!

[sunny_talwar]

And just make sure to change the expression to this:

RangeAvg(After(Sum(Amount), 0, 5))

if you plan to change the sorting from Ascending to Descending