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

[Chanty4u]

avg({<[Ref Date] ={"$(='>=' & Date(vDay-7) & '<=' & Date(vDay))"}>} AMOUNT_BU)

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

[pamaxeed]

Can you explain what you did?

What is behind the vDay variable?

[sunny_talwar]

I think you need to Add Date() function to match the formatting of Ref Date with your filter:

Avg({<[Ref Date]={">=$(=Date(Max([Ref Date])-5, 'YourRefDateFieldFormatHere'))<=$(=Date(Max([Ref Date]), 'YourRefDateFieldFormatHere'))"}>} AMOUNT_BU)

[pamaxeed]

Is not shown up anything...

[Chanty4u]

sry try dis

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

[sunny_talwar]

Did you replace YourRefDateFieldFormatHere with your date field format or did you just copy paste my expression?

[pamaxeed]

I replaced with my Format ('DD.MM.YYYY') but nothing showing up, now I have attached a sample application and used the expression suggested by sureshqv, but nothing....

[pamaxeed]

Check my attached sample application...

[sunny_talwar · Accepted solution]

Didn't realize Date was a dimension, try this:

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