1 Reply Latest reply on Nov 5, 2018 11:48 AM by Edward Ostrovsky

    Monthly average of new users by business day

    Edward Ostrovsky

      Hi all,


      My data consists of fully_enrolled_at as dimension and new_users as measure. Therefore, this doesn't require a dataset, since any date and measure you can sum() will do.


      I created the following 2 calculations:

      #this is number of calendar days in the month

      days_in_month = DATEDIFF('day',MIN(DATETRUNC('month',[fully_enrolled_at])),MAX(DATETRUNC('month',DATEADD('month',1,[fully_enrolled_at]))))


      #this changes number of calendar days in the current month to the number of days elapsed:

      days_in_all_months = IF MIN(DATETRUNC('month',today()))=MAX(DATETRUNC('month',[fully_enrolled_at])) THEN 1+DATEDIFF('day',DATETRUNC('month',today()),today()) ELSE [days_in_month] END


      Then I plotted: discrete month(fully_enrolled_at) as dimension, and sum([new_users])/[days_in_all_months] as measure.

      This gives me what I am looking for, however I would like to account for business days in each of the months, not calendar month.

      How do I remove weekends and national holidays from days_in_all_months (while current month should have # of business days elapsed)?


      Can you assist with adjusting this? Thank you!