I think I got it. Let me know if it works.
You’ll need to sub the param and the date.
ifelse(${Client}=‘Last Month’ OR ${Client}=‘Last 3 Months’ OR ${Client}=‘Last 6 Months’ OR ${Client}=‘Last Year’,
concat(
left(
toString(
formatDate(
truncDate(‘MM’,{arrival_timestamp})
,‘MMM/dd/yyyy’)
)
,3)
," ",
right(
toString(
formatDate(
truncDate(‘MM’,{arrival_timestamp})
,‘MMM/dd/yyyy’)
)
,2)
),${Client}=‘Last 4 Quaters’,
concat(
‘Q’,toString(extract(‘Q’,{arrival_timestamp}))," ",toString(extract(‘YYYY’,{arrival_timestamp}))
),
${Client}=‘Year’,
toString(extract(‘YYYY’,{arrival_timestamp})),null
)
This will return a string. So you’ll need to find a way to sort on in. I would recommend making a separate calculated field that takes the difference from now until the date as a number and sort on that average.
datDiff({date},now(),‘DD’)