Stacked bar graph using data across multiple indexes

[nekbote]

Hi All,

Need your guidance on developing below panel , here are the details...

index=index1
index=index 2
index=index 3

All the above 3 indexes represent data corresponding to different order types .

Each of the index has fields order_no , order_date , OrderAmount. All the 3 indexes have the same columns.

I need to display data from above 3 indexes in the form of stacked bar chart.

x-axis represents the age of the order [currentdate -order_date =age].
And each of the stacked bar represents sum(OrderAmount) of ordertype1 + sum(OrderAmount) of ordertype2 + sum(OrderAmount) of ordertype3

                       ordtyp1$tot  ordtyp2$tot ordtyp$tot

7 days late , 425 200 99

7 & 14 days late , 325 300 10

14 & 21 days late , 100 700 20

21 days late , 150 300 30

1st stacked bar will be summation of these 3 $ amounts[425+200+99]
2nd stacked bar will be summation of these 3 $ amounts[325+300+10]
3rd stacked bar will be summation of these 3 $ amounts[100+700+20]
4th stacked bar will be summation of these 3 $ amounts[150+300+30]

----x-axis---------- represents 7 days late , 7 &14 days late and so on

Can you guide me as to how this can be achieved

I have used below search query , it gives the half the solution , i am not able to stack the $ amounts.

Here is the search query which i have used...

index=index1 OR index=index2 OR index = index3
| fields ORDDATE,ORDDATE,ORDDATE,ORDERTOTAL,ORDERTOTAL,ORDERTOTAL,index
| eval fldNow=now()
| eval orderlinedate=strptime(substr(ORDDATE,1,8), "%Y%m%d") | eval age=((orderlinedate-fldNow)/60)/60/24

| eval age_group=case(age>-8 AND age<0,"Stale BO Less than 7 days late",age>-15 AND age<-7,"Stale BO between 7 and 14 days late",age>-22 AND age<-14,"Stale BO between 14 and 21 days late",age<-21 ,"Stale BO more than 21 days late")
| eval orderlinedate1=strptime(substr(ORDDATE,1,8), "%Y%m%d") | eval age1=((orderlinedate1-fldNow)/60)/60/24
| eval age_group1=case(age1>-8 AND age1<0,"Stale BO Less than 7 days late",age1>-15 AND age1<-7,"Stale BO between 7 and 14 days late",age1>-22 AND age1<-14,"Stale BO between 14 and 21 days late",age1<-21 ,"Stale BO more than 21 days late")
| eval orderlinedate2=strptime(substr(ORDDATE,1,8), "%Y%m%d") | eval age2=((orderlinedate2-fldNow)/60)/60/24
| eval age_group2=case(age2>-8 AND age2<0,"Stale BO Less than 7 days late",age2>-15 AND age2<-7,"Stale BO between 7 and 14 days late",age2>-22 AND age2<-14,"Stale BO between 14 and 21 days late",age2<-21 ,"Stale BO more than 21 days late")

| stats sum(ORDERTOTAL) by age_group

thanks!

[acharlieh · Accepted solution]

If I'm correct as to what you're asking, it may be as simple as instead of using stats using chart

| chart sum(ORDERTOTAL) over age_group by index

[nekbote]

Hi acharlieh,

You are bang on ...that worked ....thank you so much....you saved my day...thank you again!

[nekbote]

Hi Team,

Any suggestions on how to get this done? Any idea is appreciated...thank you!

[acharlieh]

I'm not entirely sure what you're asking... Are you wanting for each time bucket, a stacked bar graph where each bar is a particular age, and a particular segment is the sum from an individual index?

Also what does _time on each event represent? Is it ORDDATE or is it something else?

[nekbote]

Hi acharlieh,

"
Are you wanting for each time bucket, a stacked bar graph where each bar is a particular age, and a particular segment is the sum from an individual index?
"
yes you are right , each of the segment in the bar graph is the sum from individual index.

_time is the time at which event is indexed, in my specific case almost all of the events are indexed once a day between 12 am -12:05 am PST everyday.

On the x-axis in the graph , i am using the age=ORDDATE-CURRENTSYSDATE to bucket the events into 4 buckets as listed in my search query.

I have sample graph of what i need but i do not have enough karma points to post the picture.

Let me know if you need more information.

Thanks