Calculate the "month" after first appearance

[HeinzWaescher]

Hi,

I'm doing an analysis about users whose first event was in January 2014. I want to know, what they did in month 1,2,3 after their first appearance. For these users the January would be month "0". Feb would be "1", March "2" and so on...

So the goal is to add a field to every event: "month afer first appearance" with a numeric value.

Is it possible to calculate to month after the first appearance?
I already did something like this for the "day after first appearance". It looked like this:

| bucket span=1d timestamp_of_first_appearance
| bucket span=1d timestamp
| eval day=(timestamp-timestamp_of_first_appearance)/86400

But I can't do this for a monthly perspective, because the duration for the months varies

BG

Heinz

[HeinzWaescher]

Hi lguinn,

I've adjusted your approach to this and it seems to work fine:

| bucket span=1mon _time
| lookup lookup.csv userid OUTPUT first_appearance
| bucket span=1mon first_appearance

| eval month0=relative_time(first_appearance,"@mon")
| eval month1=relative_time(first_appearance,"+1mon@mon")
| eval month2=relative_time(first_appearance,"+2mon@mon")
| eval month3=relative_time(first_appearance,"+3mon@mon")
| eval month4=relative_time(first_appearance,"+4mon@mon")
| eval month5=relative_time(first_appearance,"+5mon@mon")
| eval month6=relative_time(first_appearance,"+6mon@mon")
.
.
.
| eval month_after_appearance=case(_time=month0, "0",
 _time=month1, "1",
 _time=month2, "2",
 _time=month3, "3",
 _time=month4, "4",
 _time=month5, "5",
 _time=month6, "6",)
.
.
.

| stats dc(userid) by month_after_appearance

Thanks a lot!

[lguinn2 · Accepted solution]

Why not something like this

...

| eval timestampMonth=relative_time(timestamp_of_first_appearance,"@mon")
| eval timestampMonth2=relative_time(timestamp_of_first_appearance,"+1mon@mon")
| eval timestampMonth3=relative_time(timestamp_of_first_appearance,"+2mon@mon")
| eval timestampEnd=relative_time(timestamp_of_first_appearance,"+3mon@mon")
| eval eventMonth = relative_time(timestamp_of_first_appearance,"@mon")
| eval whichMonth = case (eventMonth < timestampMonth,"before first appearance",
                                         eventMonth < timestampMonth2,"In month of appearance",
                                         eventMonth < timestampMonth3,"In second month since appearance",  
                                         eventMonth < timestampEnd,"In third month since appearance",
                                         1==1,"After third month of appearance")

[HeinzWaescher]

Thanks for your suggestion.
Unfortunately this approach will cause problems, if month_of_current<=month_of_first_appearance.

[somesoni2]

How about adding this
..your search..| eval month_of_first_appearance=tonumber(strftime(timestamp_of_first_appearance,"%m)) | eval month_of_current= tonumber(strftime(timestamp,"%m")) | eval month=if(month_of_current>month_of_first_appearance,month_of_current-month_of_first_appearance,12-month_of_first_appearance +month_of_current)