Why am I unable to display only results for last month?

[clairebesson]

Hi everyone,

I have a field that is a date. It’s written: month/day/year. I have redefined this field as _time.
So, I am able to count by month, however, when I want to see only the result for last month it didn’t work.

Here is the code to display count by month:

source="Active.csv" | bucket span=1mon _time | eval extracted_date_field = strptime('FirstDate', "%m/%d/%Y") | eval _time = 'extracted_date_field'|  eval w_month=strftime(_time, "%b/%Y")|chart count by w_month

To display only the last month I tried this:

source="Active.csv" earliest=-1month@month | bucket span=1mon _time | eval extracted_date_field = strptime('FirstDate', "%m/%d/%Y") | eval _time = 'extracted_date_field'|  eval week_month=strftime(_time, "%b/%Y")|chart count by week_month

But the results are wrong...
Could you please help me with that ? Thanks !

[somesoni2 · Accepted solution]

The time range that you applied on second query (using earliest) is only applicable to _time before your overwrite it with FirstDate field. It seems like the data from Active.csv in last 1 month, doesn't contains any dates for previous month. Give this a try

 source="Active.csv" | bucket span=1mon _time | eval extracted_date_field = strptime('FirstDate', "%m/%d/%Y") | eval _time = 'extracted_date_field' | where _time>relative_time(now(),"-1month@month") |  eval w_month=strftime(_time, "%b/%Y")|chart count by w_month

[clairebesson]

Thanks it works very well !