topic Re: Static reference line for Average calculation in QlikView

Static reference line for Average calculation

Mon, 05 Jan 2015 20:46:09 GMT

Hi,

I am trying to add a reference line in my chart to show average of sales. However, it varies based on my selection. Is it possible to make it static? I am using the formula avg(sales) in the reference line expression on Presentation tab.

Thanks,

Re: Static reference line for Average calculation

rwunderlich — Mon, 05 Jan 2015 20:56:43 GMT

Change your reference line expression to ignore selections:

avg({1}sales)

-Rob

Re: Static reference line for Average calculation

Mon, 05 Jan 2015 21:12:24 GMT

Thanks Bob! This was very helpful. However, this is working only partially. For example: In my chart, I have drill down group for my dimension (Categories and Sub-categories) on x-axis with Category values A,B,C,D - initially the average value shown by reference line is correct. But when I select any of the category (drills down to all the sub-categories within that particular category), the reference line value changes to point the average value for that category. Selecting any of the sub-categories NOW does NOT change the average value. But I want the initial average value to remain constant even if I pick any category. Is this possible?

Thanks,

Re: Static reference line for Average calculation

simondachstr — Mon, 05 Jan 2015 21:25:53 GMT

I'm not quite sure I get what you are trying to achieve but try the following expressions:

=avg(total {1} sales)

=avg(all {1} sales)

=$(=avg({1} sales))

='$(=avg({1} sales))'

This is a little bit of a try & error approach and I apologize for that but I hope one of the expressions will do.

Re: Static reference line for Average calculation

sunny_talwar — Mon, 05 Jan 2015 21:29:03 GMT

PFA an example qvw file, is this what you are trying to achieve?

Best,

Re: Static reference line for Average calculation

rwunderlich — Tue, 06 Jan 2015 00:52:22 GMT

I'm not understanding what you are asking for. could you explain it in terms of:

Clear State: avg of Categories A, B, C.

Select Cat B. Avg of Subcategories B1, B2, B3? Or something else?

-Rob

Re: Static reference line for Average calculation

Tue, 06 Jan 2015 00:54:50 GMT

Thanks Martin! =avg(total {1} sales) worked!

Thanks,