In a SearchKit query I'm looking to add a column that tells me the number of days from today to/from a date field. Basically datediff() from today.
- If the date is in the future, say tomorrow, the column would show 1 (positive).
- If the date is in the past, say yesterday, the column would show -1 (negative).
I tried the following options in Field Transformation...
- "Days to Anniversary": doesn't work if the date is more than a year away, and also doesn't work for comparing today to a date in the past (to give the negative number of days).
- "Days between two dates": there doesn't seem to be a way to set today's date as a comparison option;
I'm able to add a column "Current Date" from the Extras section, but I don't see a way to use it in the Field Transformation.
Appreciate any ideas.