Segmented Average Lines in Chart

[#1]

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!

[rustyfishbones]

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

https://www.youtube.com/watch?v=ruyfe1Mq0iU

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

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

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

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

[#3]

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

[#4]

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

[rustyfishbones]

You can add

Year (today ()) for this year

Year (today ())-1 for last year

Is that that what you mean

[rustyfishbones]

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

[rwunderlich · Accepted solution]

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

[#8]

Thank you Rob. It works!