Why am I not getting expected results using the chart command with the syntax "chart sum(fieldA) over FieldB by index"?

[nekbote]

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.

[stephane_cyrill · Accepted solution]

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

[nekbote]

Thanks alot Stephane....that solved my problem....!!!