Formula Script - Weeks since Last Date worked

OSCHPRO — Wed, 13 Nov 2024 11:25:45 GMT

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

Re: Formula Script - Weeks since Last Date worked

nhenckel — Tue, 17 Dec 2024 20:54:37 GMT

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:

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

topic Re: Formula Script - Weeks since Last Date worked in App Development

Formula Script - Weeks since Last Date worked

Re: Formula Script - Weeks since Last Date worked