I am trying to subtract a field value date (Step Due Date) from today's date (nowstring) to determine if the number of days between them is less than half the SLA value.
This is the relevant part of my query but it doesn't seem to be working:
| eval nowstring=strftime(now(), "%Y-%m-%d")
| eval "Coming Due"=case(("Step Due Date"-nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)
Here is an example value from "Step Due Date": 2018-06-18 12:11:07.890000
Hi rkassabov,
your dates are in various formats, and because of the "
the case()
statement uses literally "Step Due Date"
as string.
Try this one instead, where Step Due Date
becomes an epoch timestamp which then can be subtracted with now()
:
| makeresults
| eval SLA="2", "Step Due Date"="2018-06-18 12:11:07.890000", step_date_epoch=strptime('Step Due Date', "%F %H:%M:%S.%6N"), nowstring=now()
| eval "Coming Due"=case(('Step Due Date' - nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)
Adapt the SPL to your needs.
Hope this helps ...
cheers, MuS
Hi rkassabov,
your dates are in various formats, and because of the "
the case()
statement uses literally "Step Due Date"
as string.
Try this one instead, where Step Due Date
becomes an epoch timestamp which then can be subtracted with now()
:
| makeresults
| eval SLA="2", "Step Due Date"="2018-06-18 12:11:07.890000", step_date_epoch=strptime('Step Due Date', "%F %H:%M:%S.%6N"), nowstring=now()
| eval "Coming Due"=case(('Step Due Date' - nowstring) < (SLA / 2), 1, "Step Date Completed" != "", 0)
Adapt the SPL to your needs.
Hope this helps ...
cheers, MuS
Perfect, thank you!