7 Replies Latest reply: Jun 12, 2013 9:32 PM by Jihan Wang

# Segmented Average Lines in Chart

Hello,

I am pretty new with QlikView and now developing some dashboard for my organization. Currently, the line chart has total sale by month-year for 3 years and I would like to add an line for average sale. This average line is not the grand average across all years. For each year, the average line is the average for that year. For example, from Jan-2010 to Dec-2010, the average line is the average value for Year 2010, and from Jan-2011 to Dec-2011, the line is the average for Year 2011. Is there a way to do it. I assume this can be done with set analysis? But can someone let me know what would be the correct expression?

Thank you so much!

• ###### Re: Segmented Average Lines in Chart

this video will give you some ideas how to start doing this

Here are some examples of what the Expression Definition might look like

avg({<Year={2011}>} Value)

avg({<Year={2012}>} Value)

avg({<Year={2013}>} Value)

• ###### Re: Segmented Average Lines in Chart

Thank you Alan; however, in this case, we need to specify the year. The data will change yearly, so is there any dynamic way that would automatically change the average calculation based on available data.

Thanks,

Jihan

• ###### Re: Segmented Average Lines in Chart

Year (today ()) for this year

Year (today ())-1 for last year

Is that that what you mean

• ###### Re: Segmented Average Lines in Chart

Thank you Alan; however, in this case, we need to specify the year. The data will change yearly, so is there any dynamic way that would automatically change the average calculation based on available data.

Thanks,

Jihan

• ###### Re: Segmented Average Lines in Chart

The Expressions would be wriiten like

AVG({<Year={\$(=Year(Today()))}>}Value) for this Year

AVG({<Year={\$(=Year(Today())-1)}>}Value) for last Year

AVG({<Year={\$(=Year(Today())-2)}>}Value) for previous Year

• ###### Re: Segmented Average Lines in Chart

Do you want to display a line for the monthly average for each year? See attached. It would be something like:

=aggr(NODISTINCT

avg(

aggr(NODISTINCT Sum(TOTAL<Year,[Month-Year]>Sales),[Month-Year])

)

,Year)

-Rob

• ###### Re: Segmented Average Lines in Chart

Thank you Rob. It works!