Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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)
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
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
You can add
Year (today ()) for this year
Year (today ())-1 for last year
Is that that what you mean
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
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
Thank you Rob. It works!