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




No comments:

Post a Comment

Page-level ad