How do you create a line graph which shows 3 values?

[QuintonS]

Rookie Question: I am trying to create a line graph showing 3 values. i have the query which works perfectly to show "ratings" per site for each site per week. But i want to show the overall rating for both sites as well.

here is the query i use..

| eval week=relative_time(_time,"@w1")
| eval week=strftime(week,"%V")
| chart avg(overall_rating) over week by area

area= field name and contains values for 2 sites. if i remove "by area" then i get the overall rating for both sites and i want to get that showing in the same graph.

please help a newbie!! 🙂

[renjith_nair · Accepted solution]

@QuintonS,

If are looking for just total over week then, try

| eval week=relative_time(_time,"@w1")
| eval week=strftime(week,"%V")
| chart avg(overall_rating) over week by area
| addtotals

Updated:

  | eval week=relative_time(_time,"@w1")
  | eval week=strftime(week,"%V")
  | eventstats avg(overall_rating) as OVERALL_RATING
  | chart avg(overall_rating),max(OVERALL_RATING) as OVERALL_RATING over week by area
  | rename "avg(overall_rating): *" as *,"OVERALL_RATING : *" as DEL*|foreach DEL*[eval OVERALL_RATING =<<FIELD>>]|fields - DEL*

[QuintonS]

Hi Renjith, not looking for the totals.

output i want should look like the following.

Week, Site1, Site2, Overall rating

hope this makes sens?

[renjith_nair]

So is it not Site1_Rating+Site2_Rating? May be a sample data will be helpful. Sorry for that.

[QuintonS]

i need to provide average of ratings for the client. so i have daily data with a "overal_rating" field. and i also have data per site. So i need to show average overall rating and average overall rating per site in the same graph. cant share sample data unfortunatley..

[renjith_nair]

Okie, calculate this value before chart and add it in chart

 | eval week=relative_time(_time,"@w1")
 | eval week=strftime(week,"%V")
 | eventstats avg(overall_rating) as OVERALL_RATING
 | chart avg(overall_rating),max(OVERALL_RATING) as OVERALL_RATING over week by area

[renjith_nair]

Added little clean up 🙂

    |rename "avg(overall_rating): *" as *,"OVERALL_RATING : *" as DEL*|foreach DEL*[eval OVERALL_RATING =<<FIELD>>]|fields - DEL*

[QuintonS]

This is very close, need to do some tweeks. seems to be working.

Thanks so much for the help! 🙂

[renjith_nair]

You are welcome @QuintonS,. Updated the answer, please accept if it's ok