Hi All,
I am having issue with the search below. Hope you can point out where i am going wrong.
index=index1 OR index=index2 OR index = index3 1234 OR 12345 OR 123456
| fields ORDERDATE,ORDERDATE,ORDERCREATEDATE,MERCHLINETOT,MERCHLINETOT,MERCHLINETOT,index
| eval fldNow=now()
| eval ORDERCREATEDATE1=substr(ORDERCREATEDATE,7,10)
| eval age=((ORDERCREATEDATE1-fldNow)/60)/60/24
| eval orderlinedate1=strptime(substr(ORDERDATE,1,8), "%Y%m%d")
| eval age1=((orderlinedate1-fldNow)/60)/60/24
| eval orderlinedate2=strptime(substr(ORDERDATE,1,8), "%Y%m%d")
| eval age2=((orderlinedate2-fldNow)/60)/60/24
| where (age <0 OR age1 <0 OR age2 <0 )
| eval age_group=case(age>-8 AND age<0,"Less than 7 days late",age>-15 AND age<-7," 7 and 14 days late",age>-22 AND age<-14,"between 14 and 21 days late",age<-21 ,"more than 21 days late")
| eval age_group1=case(age1>-8 AND age1<0,"Less than 7 days late",age1>-15 AND age1<-7," 7 and 14 days late",age1>-22 AND age1<-14," between 14 and 21 days late",age1<-21 ,"more than 21 days late")
| eval age_group2=case(age2>-8 AND age2<0,"Less than 7 days late",age2>-15 AND age2<-7,"7 and 14 days late",age2>-22 AND age2<-14," between 14 and 21 days late",age2<-21 ,"more than 21 days late")
| eval grouped_fields=coalesce(MERCHLINETOT,MERCHLINETOT,MERCHLINETOT)
| chart sum(MERCHLINETOT) over age_group2 by index
| rename index1 as "StaleA", index2 as "StaleB", index3 as "StaleC"
age= AGE OF 1234 = -1.5
age1=AGE OF 12345 = -2.5
age2=AGE OF 123456 = -3.5
ALL THE ABOVE AGES fall under "Less than 7 days late" age_group category.
Problem:
When I use:
chart sum(MERCHLINETOT) over age_group2 by index
or
chart sum(MERCHLINETOT) over age_group1 by index
I see the result only for StaleA and StaleB
age_group1 StaleA StaleB
Less than 7 days late 79.70 95.92
When I use:
chart sum(MERCHLINETOT) over age_group by index
I see the result only for StaleC
age_group StaleC
Less than 7 days late 99.70
Why am i not seeing the results for all 3 [StaleA , StaleB , Stale C] ? What am i missing? Any guidance is really appreciated.
Thank you.
Expected result
age_group StaleA StaleB StaleC
Less than 7 days late 79.70 95.92 99.70
Kindly let me know if you need more information.
Hi nekbote,
your are using many sources of data ( many index) .
Try to COALESCE all the values of age_group1,age_group,........... in one global_age_group and do the chart sum(...) over global_age_group
Hi nekbote,
your are using many sources of data ( many index) .
Try to COALESCE all the values of age_group1,age_group,........... in one global_age_group and do the chart sum(...) over global_age_group
Thanks alot Stephane....that solved my problem....!!!