3 month moving with current month for a date column

gbrown
gbrown Member
edited March 2023 in SQL DataFlows

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

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @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
    gbrown Member

    @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?


    image.png


  • MichelleH
    MichelleH Coach

    @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
    gbrown Member

    @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

    image.png

    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
    MichelleH Coach

    @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
    MarkSnodgrass Coach

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories