90 Day rolling average

[shearwatertroy]

Hey again guys,

I'm now working on a 90 day rolling average.

I've tried a variety of solutions listed on here, but maybe using the fields I am working with, I have messed up the syntax (I'm hoping it's something as simple as that).

I've tried using these equations:

Set Analysis for certain Point in Time

More specifically, the one I have been working with the most is:

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )

Here's what I have adapted

Sum( {<Date={‘>=$(=Date(Today()-89))<=$(=Today())’}>} [Line Amount] )

So I'm trying to total up the Line Amount field for the last 90 days. I get null values.

I have a couple other places to use this within an if statement as well.

Any help would be greatly appreciated.

Cheers,

Troy

Message was edited by: Troy Strachan July 8 2016

[shearwatertroy]

I've already tried that.

Could it be due to having multiple account numbers and they are cancelling each other out? I'm grasping at straws here. 😕 I have to use this equation multiple times on a range of AccountNum

[sunny_talwar]

To tell you the truth, I have no idea and it would be difficult to comment without looking at it. I am really sorry. I want to help, but not really sure what I can do

[harithadogiparthi505]

Hello Sunny,

I am getting an error when representing 90 day rolling average in pivot table.

avg(aggr(avg({<date={">=$(=date(max(date)-90,'MM/DD/YYYY')) <=$(=date(max(date),'MM/DD/YYYY'))"}>}COUNT),[File Number],[Day]))

when selecting one week dates in date range picker, the values were good enough.

when selecting 2 weeks in date range picker the first week values are shown but wrong values and second week values are showing null in pivot table.

Kindly help me in resolving this issue when calculating 90 days rolling average.

Any help greatly appreciated.