the code I wrote for it:
"start date" - format : 2021-03-22 f
"last_6_months". - calculated now()-6 month
below the query we tried but its not working
could you please advise?
`ssdlc_base("all time back")`
| where email_address!="NA"
| lookup ssdlc_bu_courses.csv BU output courses
| mvexpand courses
| lookup ssdlc_bu_courses.csv BU courses output "start date"
| rename "start date" as "start_date1"
| mvexpand "start_date1"
| eval start_date=strptime(strftime(start_date1,"%Y-%m-%d")."-01","%Y-%m-%d")
| eval last_6_months=strptime(strftime(relative_time(now(),"-6mon"),"%Y-%m")."-01","%Y-%m-%d")
| fields start_date ,last_6_months , start_date1
Playing around with some results it doesn't seem to affect anything, but I was also concerned about the ."-01" in this line from your original post, the resulting output of the strftime() function wouldn't fit the format. You'd end up with something like 2021-03-22-01.
| eval start_date=strptime(strftime(start_date1,"%Y-%m-%d")."-01","%Y-%m-%d")
I had a couple questions in your start date format, what does the "f" at the end of the date format mean?
Can you provide a sample value of "start_date1" from just after mvexpand "start_date1" but before you perform any evals on it?
not working the dates are not in the same format.
| eval start_date=relative_time(strptime(start_date1,"%Y-%m-%d"),"@mon")
| eval last_6_months=relative_time(now(),"-6mon@mon")
still no change:
The field you are showing is "start date" not start_date. What is is start_date and start_date1?
no data for start_date
That's because you removed the rename you had previously. Either put it back in or use
| eval start_date=relative_time(strptime('start date',"%Y-%m-%d"),"@mon")
| eval last_6_months=relative_time(now(),"-6mon@mon")
Try changing these two lines
| eval start_date=relative_time(start_date1,"@mon")
| eval last_6_months=relative_time(now(),"-6mon@mon")