Calculate How Long Activities Take

Example: Display the Number of Days Since an Opportunity Opened

Suppose that you have an opportunity dataset with the account name and the epoch seconds fields:

You want to see how many days ago an opportunity was opened. Use daysBetween() and now(). Use toDate() to convert the order date epoch seconds to a date format that can be passed to daysBetween().

q = load "OpsDates1";

q = foreach q generate Account, daysBetween(toDate(OrderDate_sec_epoch), now()) as 'daysOpened';

The resulting data stream displays the number of days since the opportunity was opened.

Example - How Many Weeks Did Each Opportunity Take to Close?

Use date_diff() with datepart = week to calculate how long, in weeks, it took to close each opportunity.

q = load "DTC_Opportunity";
q = foreach q generate date_diff("week", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch) ) as 'Weeks to Close';
q = order q by 'Weeks to Close';