3 month moving with current month for a date column

gbrown

I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR.

I know the current month function to get only the current month moving but having trouble integrating the following two months as well with it.

Let me know your thoughts...

Thanks

MichelleH

@gbrown You will need to use a Window function for this calculation, although those functions are not supported in Domo's version of MySQL. You would have to use either the Rank & Window ETL tile or write it in a beast mode.

gbrown

@MichelleH @MarkSnodgrass How would I go about this in a window function under the rank and window tile? or do you know what the beastmode would look like?

MichelleH

@gbrown Here is the knowledge base article on how to configure the Rank & Window tile in ETL: https://domo-support.domo.com/s/article/360044876094?language=en_US

For your situation, you would want to use a framed average or sum, ordered by Date. To calculate the 3-month average you would set Preceding = 0 (start with current month) and Following = 2 (calculate through two months from now).

gbrown

@MichelleH Is there a way to do a filter formula to calculate without the rank and window tile. it can be in a beastmode if needed. Something similar to

CASE WHEN YEAR(`Date`) >= YEAR(CURDATE()) AND MONTH(`Date`) <= ADDDATE(CURDATE(), INTERVAL 3 MONTH)

THEN 'SHOW'

ELSE 'HIDE'

END

The one problem is that it is picking up end months for 2023 and start for 2024 ( I am not sure why) but it seems like it could work maybe my logic needs help.

Thanks

MichelleH

@gbrown A beast mode filter would work, with a couple changes:

CASE `Date` <= DATE_ADD(CURDATE(), INTERVAL 2 MONTH) and `Date` > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

THEN 'SHOW'

ELSE 'HIDE'

END

MarkSnodgrass

@gbrown if you are only wanting to show the current month and the next two months and wanting to show the average of those values, I think you could keep it pretty simple by using the last_day function and the date_add function like this:

CASE when LAST_DAY(`dt`) >= LAST_DAY(CURRENT_DATE()) AND LAST_DAY(`dt`) <= LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 3 month)) 
THEN 'Inside 90-day window'
ELSE 'Outside 90-day window'
END

You would then add this to your filter and filter to "Inside 90-day window" and then drag your value field into your table and choose Average for the aggregation type.