Count the number of times one day appears in a month

Natalia
Natalia Member
in Magic ETL

How can I count the number of Mondays (also need it for Tuesdays, Wednesdays, etc.) in the previous month?

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    Probably need a little more context as to what you are trying to do, but generally you could do this:

    In your card, set the date range filter to Previous Month

    Create a beast mode that uses the DAYNAME() function to return the name of the day of a given date.

    Drag that beast mode into your card and then drag it again and set the aggregate function to Count.

    This assumes you only have one row per date.

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

    I have some values for the whole month, and need to find the values for an average Monday, so I added all the values but now I need to divide those values by the number of Mondays. I have tried using average but the values are wrong, the only way to get the values I am looking for is to divide those values by the number of Mondays in that month, its there anyway I can create a Beastmode formula that will return the number of Mondays in the previous month?

  • Ritwik
    Ritwik Contributor
    edited May 2021

    @Natalia


    Does the dataset have data for every day, always? If there is no events/value for a given Monday (Say Monday, 5/3) , is there still a row of data for Monday, 5/3?

  • Natalia
    Natalia Member

    Yes, there are multiple Mondays in the data though so for Monday, 5/3 there are 5 rows with the same date but different data in the other columns

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Use a CASE statement. CASE when <dayOfWeek> = 'Monday' then 1 else 0 end.

    You could alternatively do crazy math where you do the Number of days in a month %% 7 and then do math on the remainder to figure out if the remainder would include another Monday.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DataUp
    DataUp Member

    This will give you the dominator


    CASE


    WHEN DAYNAME(`Date`) = 'Sunday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Monday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Tuesday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Wednesday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Thursday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Friday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


     WHEN DAYNAME(`Date`) = 'Saturday' THEN COUNT(DISTINCT CONCAT(`Date`,DAYNAME(`Date`)))


    END

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @DataUp , your beast mode will not behave as expected UNLESS you have the day of the week (Sun, Mon Tues) on the axis. At which point your CASE statement is superfluous and you could just do a count(distinct on dates).


    Even that aside, conceptually you're mixing up two things. In your solution, you've combined what should be two separate beast modes into one.

    Your CASE statement should be used to define a column (the name of the day of the week) which you can display as an axis on your report. then the COUNT DISTINCT should be used as the metric.


    Imagine what'd happen if you did not have the name of the day of the week on the axis. In one row you'd have ALL the days of the month. So then when your beast mode evaluates for the days that are a Monday you get 5, for the days that are Tuesday you'd get 4 and wednesday a 4... so then what should analyzer do with those results? SUM them up? you'd end up with 31 :P


    Last best practices thing. be careful to avoid nesting beast modes inside a CASE statement. keep in mind, if you perform a CASE statement outside the aggregation (your COUNT DISTINCT) then theoretically the aggregation happens BEFORE the CASE statement has been evaluatated. In other words, you'll calculate Count distinct before you've tested what day of the week it is. (at which point you might be thinking ... 'but i evaluated the date inside the count disticnt, which returns us to my initial statement that the CASE statementt is currently superfluous.)


    hope that makes sense.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories