topic Re: Segmented Average Lines in Chart in QlikView

Segmented Average Lines in Chart

Mon, 10 Jun 2013 16:55:00 GMT

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

rustyfishbones — Mon, 10 Jun 2013 18:07:00 GMT

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)

Re: Segmented Average Lines in Chart

Mon, 10 Jun 2013 21:12:21 GMT

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

Mon, 10 Jun 2013 21:12:32 GMT

Thanks,

Jihan

Re: Segmented Average Lines in Chart

rustyfishbones — Mon, 10 Jun 2013 21:22:27 GMT

You can add

Year (today ()) for this year

Year (today ())-1 for last year

Is that that what you mean

Re: Segmented Average Lines in Chart

rustyfishbones — Mon, 10 Jun 2013 21:51:14 GMT

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

rwunderlich — Mon, 10 Jun 2013 22:16:56 GMT

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

Thu, 13 Jun 2013 01:32:49 GMT

Thank you Rob. It works!