How to get the 6 month ago column from field in lookup?

[urapaveerapan]

Hi,

I have a column named Month in lookup file

For example,
Month
2017/02
2017/01
2017/01
2017/01
2016/12
2016/12

I need to get the maximun month and the month in 6 month ago
ex. Maximum month = 2017/04, 6 month ago=2016/10

I tried strftime, strptime, relative_time but it's not working.
Please help.

[gcusello]

Hi
you have to transform your month in epochtime and then find max and calculate six months later, try something like this:

| inputlookup your_lookup 
| eval Month=Month+"/01" 
| eval date_month1=strptime(Month,"%Y/%m/%d")
| stats max(date_month) AS max_month
| eval Max_Month=strftime(max_month,"%Y/%m")
| eval Month_6_later=relative_time(Max_Month,"+6mon"), Date_Month_6_later=strftime(Month_6_later,"%Y/%m")
| table Max_Month Date_Month_6_later

Bye.
Giuseppe

[urapaveerapan]

| inputlookup pcm_incoming_ticket_lookup
| eval Month=Month+"/01"
| eval date_month1=strptime(Month,"%Y/%m/%d")
|table Month, data_month1

No data shown in data_month1 column but Month work fine.

[gcusello]

beware that in eval you have "date_month1", instead in table you have "data_month1", they are different ("data" instead "date").
Bye.
Giuseppe