1 Reply Latest reply on Nov 27, 2012 9:46 AM by Tracy Rodgers

# Is there a way to do something similar to excel's AVERAGEIF statement?

I'm trying to do a moving average of a time series with hours and days. I'd like to have each hour's individual rolling average going back 7 days, and then perform calculations based on this average.

In excel this is performed rather easy with a rolling AVERAGEIF statement but the I've not figured out a simple way of doing this in tableau. I have figured out some complex ways of doing it but they are slow, and somewhat unreliable.

Any help would be much appreciated.

Thanks.

• ###### 1. Re: Is there a way to do something similar to excel's AVERAGEIF statement?

Hi Philip,

The iif function can most likely be used. Would something similar to the following give the desired results?

iif(attr([Order Date])<=today() and attr([Order Date])>=dateadd('day', -7, today()) , sum(Profit)/7, null)

I'm not sure how you'd like to do hours, but this could be further included in the calculation by referencing the specific hour(s).

Hope this helps a little bit!

-Tracy