Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
Please see my record sets below. I want to able to retrieve the records between and including the first and last Booking Dates for each month (highlighted in yellow).
Please note that the first and last Booking Dates for each month are different.
Any help would be appreciated.
Regards,
Magen
Do you mean to say that first and last date from only highlighted sections?
You can use FirstSortedValue() function both either in script or in UI level..
see attachment
if your table is Tmp1, the expression to identify the records should be (bold)
Final:
load
ServiceRenderedDate, Sales, BookingDate,
if(not IsNull(BookingDate),
alt(peek(Flag),0)+1,
if(peek(Flag)=2, null(), peek(Flag)
)) as Flag // 1 or 2 for record included
Resident Tmp1
order by ServiceRenderedDate;
P.S.: maybe you have to replace isnull(BookingDate) with some other function (len(trim(BookingDate))>0)