Calculate Week Days, Days Elapsed and Days Remaini...

DickyMohanty · 12-07-2020 04:46 AM

Hi Team,

Can we built dimensions for ‘Week Days’ (excluding Sat and Sun), ‘Week Days Elapsed’, ‘Week Days Remaining’ for the Current month?

When the Looker Dashboard will be opened, based on the Month-Year, it will dynamically calculate these 3 KPIs.

For example today is 12/07/2020:

Week Days: 23 (can be 22 also, if Dec 25,holiday is excluded)

Days Elapsed: 4

Days Remaining: 19

I was able to generate Week Days of a selected Time Range from Filter pane (using start_date and end_date), using this below script. But this will be used as a second option, if the above ones looks difficult (calculating dynamically).

My Database is BigQuery.

filter: date_filter {
type: date
}

dimension_group: filter_start_date {
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2015-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
}

dimension_group: filter_end_date {
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
}

dimension: week_days {
type: number
sql:
DATE_DIFF(${filter_end_date_raw}, ${filter_start_date_raw}, DAY) + 1 -
DATE_DIFF(${filter_end_date_raw}, DATE_ADD( ${filter_start_date_raw},INTERVAL 1 DAY), WEEK) -
DATE_DIFF(${filter_end_date_raw}, ${filter_start_date_raw}, WEEK) ;;
}

DickyMohanty

Hello Experts,

Any suggestions, help in his regards!

Thank you.

DickyMohanty

Hello Experts,

Any possible ideas, suggestions here?

Thank you.

DickyMohanty

Hello,

I was able to achieve this. Below is the LookML:

dimension: month_end {
type: date
sql: LAST_DAY(CURRENT_DATE, MONTH) ;;
}

dimension: month_start {
type: date
sql: DATE_SUB(LAST_DAY(CURRENT_DATE, MONTH) + 1, INTERVAL 1 MONTH) ;;
}

dimension: current_date {
type: date
sql: CURRENT_DATE ;;
}

dimension: business_days {
type: number
sql:
DATE_DIFF(${month_end}, ${month_start}, DAY) + 1 -
DATE_DIFF(${month_end}, DATE_ADD( ${month_start},INTERVAL 1 DAY), WEEK) -
DATE_DIFF(${month_end}, ${month_start}, WEEK)
;;
}

dimension: business_days_elapsed {
type: number
sql:
DATE_DIFF(${current_date}, ${month_start}, DAY) -
DATE_DIFF(${current_date}, DATE_ADD( ${month_start},INTERVAL 1 DAY), WEEK) -
DATE_DIFF(${current_date}, ${month_start}, WEEK)
;;
}

dimension: business_days_remaining {
type: number
sql:
${business_days} - ${business_days_elapsed}
;;
}

Dawid

You could use a dimension of the type “duration” but it wouldn’t help you with business days though

DickyMohanty

@Dawid ,

Yeah, rather than the Duration, I was more focusing on the Week/Business Days, excluding Saturday and Sunday.

Also, my next target is to exclude the Calendar Holidays also. I want to manually add the Holiday Dates ins some dimension/variable and exclude from the Business Days calculations.

For example: December 25th is a Holiday, so the Business Days in December month would be 22, rather than 23.

Any insights here?

Dawid

I exclude weekends from certain calculations using calendar table where a date has a boolean value “is_weekday”

You can also add holidays to such calendar table and then create another field “is_business_day”..

Then such table could be even further expanded and for each date have “working_day_in_month” that is a running total, or “working_days_in_month” that is total working days for a month for that date.

Calendar table would be your saviour here : )

DickyMohanty

@Dawid ,

I do have a Time Period table, where I am building my new Dimensions, what I had shared.

Regarding these Boolean vaue: “is_weekday”, “is_business_day” ; can you share some example, how to add these, and ultimately add “working_day_in_month”.

I am still new and learning.

Dawid

This is how I would do it in BigQuery:

WITH base AS (
  SELECT
    date,
    DATE_DIFF(date, CURRENT_DATE(), DAY) AS day_offset,
    COALESCE(NULLIF(EXTRACT(DAYOFWEEK FROM date) - 1, 0), 7) < 6 AS is_weekday,
    EXTRACT(DAY FROM date) AS day_of_month,
    DATE_TRUNC(date, MONTH) AS first_day_of_month,
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-01-31', INTERVAL 1 DAY)) AS date
)

SELECT
  date,
  is_weekday,
  day_of_month,
  IF(is_weekday,
    COUNT(NULLIF(is_weekday, FALSE)) OVER(PARTITION BY first_day_of_month ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    NULL
  ) AS working_day_in_month,
  COUNT(NULLIF(is_weekday, FALSE)) OVER(PARTITION BY first_day_of_month) AS working_days_in_month
FROM base

These queries would give me a nice table like the following:

NULLs for weekends are intentional. In order to take holidays into consideration, you would need to plug in a table with dates to your calendar and consider additional check like

IF(holidays.date IS NOT NULL, FALSE, is_weekday) AS is_weekday

DickyMohanty

@Dawid ,

Ohh this is an awesome piece of work. you have been a brilliant mentor now

I am giving my hands-on it now and learning.

If I understand correctly, you did this in BigQuery to get a View created so we can bring into Looker and use it with other Time Period table.

Can we also use directly in LookML to create a new View and join in regular Explore, with regular ‘sql_on:’ keyword?

Dawid

Once I build a calendar table like this, I end up using it everywhere. In this case, I did both:

I have a table that is joined in my, for example, aggregation data model
I have a view that points directly to calendar table and then I join it to other views in order to create different metrics like “was the order yesterday?”. I join order_date with calendar and create boolean field “calendar.day_offset = -1

Calculate Week Days, Days Elapsed and Days Remaining for Current Month