Trying to get relative dates in calculations

Hi everyone,

I’m trying to do a calculation where I get the average of a particular period of time.

This has to be automated. I need the calculation to take the last 6 months data and ignore the previous 2 months data.

I cant do this at the table level because of the other part of this calculation.

If i did this at the table level, would it be possible to also have a sum over and say this calculation only took into account the last 2 months data thats being excluded from the top?

Hopefully this makes sense.

Cheers
Jake

You could use the calculated field below if you want to get the average of a field called {measure} where the date field called {date} has values between 6 months ago and 2 months ago:

avg(
  ifelse( 
    {date} > addDateTime(-6, 'MM', now()) AND {date} ​< addDateTime(-2, 'MM', now()),
    {measure},
    NULL
  )
)

Want to correct darcoli’s answer. Need to replace 0 with NULL, otherwise you will get lower absolute value of average.

The expression should be
avg(
ifelse(
{date} > addDateTime(-6, ‘MM’, now()) AND {date} ​< addDateTime(-2, ‘MM’, now()),
{measure},
NULL
)
)

Please let us know if this solution works for you or if you want to compute average at a different level of details (use avgOver function instead)

1 Like

Yep, good catch! :slight_smile: (it’s not allowing me to fix my original answer)