Count the number of times one day appears in a month

Natalia
Natalia ⚪️

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔵

    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.

  • Natalia
    Natalia ⚪️

    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 Atlanta 🟡
    edited May 5

    @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 ⚪️

    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 Budapest / Portland, OR 🔵

    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.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!