How to calculate the occurrence of a mondays in a given specific time period

[Sneha_Gurav]

Hi, how do I calculate the occurrence of a weekday in a given specific time period,

For example, for past 2 months I want calculate the count of mondays, tuesdays and so on in this time period

[Cyril_MTL_Analy]

You can do that easily either using custom fields or building a measure like so:

[Sneha_Gurav]

Hi Cyril,
Thanks for your quick response.
In a case if there is no order placed for any specific day
For eg: so say in past 7 weeks there are 7 mondays, but no orders are available for one monday , then my count of mondays would be 6 , but I need to calculate the no of mondays irrespective of orders not available for the specific monday. can this calculated measure or field would return all the 7 mondays count?

[bens1]

Sneha,

I went back to basics on this and made a Date Table in Excel, added it to my Data Warehouse, and I regularly join it to my Explores to do things like this. Even if it’s a pure list of dates (the most basic of Dates Tables), once you bring it into Looker and make a dimension_group out of it, this becomes trivial.

[Cyril_MTL_Analy]

Yes exactly like @bens1 said.
You’ll only get counts if the date exists in your DW/DB.

So I’ll recommend building a date table either from a flat file (excel/google spreadsheet) and ingesting that into your DW or generating the table in SQL using something like this (BigQuery Standard SQL):

SELECT
      day
FROM
      UNNEST(
            GENERATE_DATE_ARRAY(
               DATE('2019-01-01')
               , DATE('2025-12-31')
               , INTERVAL 1 DAY
            )
      ) AS day
ORDER BY day