Formula Script - Weeks since Last Date worked

[OSCHPRO]

I have a script formula that return the 1st date a unique ID appear, how do I add a script to get the weeks from the Last Date against that Unique ID?

CandidateUniq as CandidateUniqID,

if(Max(Floor((Today() - WeekStart(BookingDate))/7))>=0,Max(Floor((Today() - WeekStart(BookingDate))/7)),0) as [WeeksSinceFirstBooked]

resident Bookings

[nhenckel]

I want to make sure I understand your question correctly. It sounds like you’re asking for a way to calculate the weeks since the last (most recent) date for each unique ID, in addition to your existing calculation for the first date.

If that’s the case, you can try this adjustment to your script:

CandidateUniq as CandidateUniqID,

// Weeks since the first booked date
if(Max(Floor((Today() - WeekStart(BookingDate))/7))>=0, 
   Max(Floor((Today() - WeekStart(BookingDate))/7)), 
   0) as [WeeksSinceFirstBooked],

// Weeks since the last booked date
if(Floor((Today() - Max(BookingDate))/7)>=0, 
   Floor((Today() - Max(BookingDate))/7), 
   0) as [WeeksSinceLastBooked]

resident Bookings
Group By CandidateUniq;

This will give you both:

1. WeeksSinceFirstBooked – Weeks since the first date a unique ID appeared.
2. WeeksSinceLastBooked – Weeks since the last (most recent) date a unique ID appeared.