Is there a Beastmode I can configure for Rolling 30 period?

MichaelClark
MichaelClark Member
in Magic ETL

The caveat is the 30 day period I want to configure is Previous Month Second Tuesday to Current Month Second Tuesday.

Any assistance is appreciated in advance.

Thanks

Michael

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    To determine the 2nd Tuesday of the month, you could use the DAYNAME function and the DAY function. The 2nd Tuesday of the month can only occur between the 8th and the 4th of the month, so you could write a beast mode like this:

    CASE WHEN DAY('date') >= 8 AND DAY('date') <=14 AND DAYNAME('date') = 'Tuesday' THEN '2nd Tuesday'
ELSE 'Some other day'
END


    You could also use DATE_SUB with the current date to go back one month

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    I would look at this post by @GrantSmith who lays out this functionality nicely.

    https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages


    **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.
  • MichaelClark
    MichaelClark Member

    This is great information in regards to calculating rolling average however, what I am attempting to is create a time period/date function for Microsoft Patch Tuesday's that looks at "last_sync" date field with "Review Period is between Previous Month Second Tuesday to Current Month Second Tuesday = True".

    One idea I had was to create a Beast Mode with CASE statement to set the current year (and subsequent 4-5 years) month's second Tuesday's something like this:

    CASE when 'last_sync' = '05-11-2021' then 'May 2021 Patch Tuesday' else

    when 'last_sync' = '06-08-2021' then 'June 2021 Patch Tuesday' else

    and continuing through the year(s).

    The difficulty is the review period is not Last 30 days, which would be easy enough, but second Tuesday to second Tuesday.

    Thanks!

    Michael

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    To determine the 2nd Tuesday of the month, you could use the DAYNAME function and the DAY function. The 2nd Tuesday of the month can only occur between the 8th and the 4th of the month, so you could write a beast mode like this:

    CASE WHEN DAY('date') >= 8 AND DAY('date') <=14 AND DAYNAME('date') = 'Tuesday' THEN '2nd Tuesday'
ELSE 'Some other day'
END


    You could also use DATE_SUB with the current date to go back one month

    **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