Count the number of times one day appears in a month

Natalia

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

MarkSnodgrass

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

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

@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

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

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.

DataUp

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

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