Pages

Friday, February 21, 2025

CRM Analytics: Calculate aging excluding weekends in Dataflow

We shared the Salesforce formula field to calculate aging without the weekend here. But can we replicate it in CRM Analytics? 

Here is the formula using dataflow in compute expression:

Full_Weeks (in Numeric)
floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7) * 5

Remaining_Days (in Numeric)
date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) % 7

Start_Day (in Numeric)
day_in_week(toDate(CreatedDate_sec_epoch))

Weekend_Adjustment (in Numeric)
case when (Start_Day + Remaining_Days) > 7 then 2 
     when (Start_Day + Remaining_Days) == 7 then 1 else 0 end

Business_Days_Aging
Full_Weeks + Remaining_Days - Weekend_Adjustment


Notes:
  • The floor(numeric) function rounds a numeric value down to the nearest integer. For example, floor(125.625) will return 125.
  • For more information and samples on date_diff() function, check out this blog Duration between Date or Date/Time Fields
  • The day_in_week(date) function returns the day of the week as numeric, where Sunday = 1, Monday = 2, etc.

Sample:







No comments:

Post a Comment

Page-level ad