Day of week

[taori-rohan]

I want to create a measure which shows the number of business days between 2 specified dates. The problem Im facing is with DAYNAME function. I want to use a case when statement. It looks something like this

measure: business_days{

view_label: “XYZ”

label: “01.ABC”

type: average

SQL: CASE WHEN DAYNAME($start_date) != ‘Sun’ THEN 1 ELSE 0. 

}

The above DAYNAME function does not work. Is there a way to tackle this problem? 

[Dawid]

This depends on your SQL dialect. You’re using a function that doesn’t exist in your SQL. What dialect do you use?

But regardless of your dialect your start_date dimension, if it’s created as a dimension_group of type time, you can add a timeframe day_of_week and use it in subsequent dimension/measures.

dimension_group: start_date {

    type: time

    timeframes: [day_of_week, day_of_week_index]

    datatype: date

}

Then you can reference this value as ${start_date_day_of_week}. Looker uses Monday - Sunday 0-6 notation

[taori-rohan]

I was able to follow the above logic and it works like a charm. The only change that I did was datatype: datetime as my dates are in datetime format.