Business day metric 1

[thaan]

Hello I am trying to create a that will count only the business days of a month. For example Mon-Sat but not count the sundays. Any suggestions?
Thank You

 

[JaC74]

If you can make table changes, you can have an ID such as DayOfWeekID. With this column, you can filter you count metric on the DayOfWeekIDs. For example, Monday = 1, Tuesday = 2, Wednesday = 3, etc. Your metric will Count Date where DayOfWeekID Not In (7). 7 being Sunday.

Or, if you are using this metric to divide revenue for a specific time period, then you may be able to count distinct Date from your fact table if your fact tables only have data on business days.

 

[MSIsam]

Another way is to submit a SQL statement that can extract the day of week from whatever date you need. It's a bit messy, but it works. Here is an example of the Teradata SQL that returns the day of week for current date.

SELECT
  current_date,
  (((  case
    when (((current_date mod 10000) / 100) > 2) then
      (146097 * ((current_date/10000 + 1900) / 100)) / 4
      +(1461 * ((current_date/10000 + 1900) - ((current_date/10000 + 1900) / 100)*100) ) / 4
      +(153 * (((current_date mod 10000)/100) - 3) + 2) / 5
      + current_date mod 100 - 693901
    else
      (146097 * (((current_date/10000 + 1900) - 1) / 100)) / 4
      +(1461 * (((current_date/10000 + 1900) - 1) - (((current_date/10000 + 1900) - 1) / 100)*100) ) / 4
      +(153 * (((current_date mod 10000)/100) + 9) + 2) / 5
      + current_date mod 100 - 693901
  end) + 0) mod 7 + 1) day_of_wk

Result:

	Current_Date()	day_of_wk
	2004-05-11	    3

Placing this into a view could avoid extra ETL

You can then create a filter in the same manner that JaC74 suggested.

 

[bridgeconsultant]

Since we're using Teradata, there is an easier way. Just join to the calendar table:

sel * from sys_calendar.calendar where calendar_date='2004-05-15';

Will give you day of week (7):

calendar_date day_of_week day_of_month day_of_year day_of_calendar weekday_of_month week_of_month week_of_year week_of_calendar month_of_quarter month_of_year month_of_calendar quarter_of_year quarter_of_calendar year_of_calendar
2004-05-15 7 15 136 38121 3 2 19 5445 2 5 1253 2 418 2004

 

[entaroadun]

Just want to inject one tidbit for performance:

You can execute a count of business days using a metric with the criteria embedded in a CASE statement, rather than using a filter in the metric.

Not using the filter will cause MSTR to do the count in the same pass as other metrics, saving you time.