compare 6 months ago to start date(can be any specific date)

[rivkah01]

we need to check which courses were started during the month of last half a year.

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

[Micheal_S]

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")

[Micheal_S]

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? 

[rivkah01]

not working the dates are not in the same format.

[ITWhisperer]

| eval start_date=relative_time(strptime(start_date1,"%Y-%m-%d"),"@mon")
| eval last_6_months=relative_time(now(),"-6mon@mon")

[rivkah01]

still no change:

[ITWhisperer]

The field you are showing is "start date" not start_date. What is is start_date and start_date1?

[rivkah01]

no data for start_date

[ITWhisperer]

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")

[ITWhisperer]

Try changing these two lines

| eval start_date=relative_time(start_date1,"@mon")
| eval last_6_months=relative_time(now(),"-6mon@mon")