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
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