Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
peterderrington
Contributor

How to calculate an average reference line (in %) for a run chart

Hi,

I've created a simple run chart but have run into a small problem - i cant create the reference line mentioned above.

The graph itself is a simple bar/line chart with the line showing the compliance of a task by a given location in percentage form.

The expression for that line is:

Count({<Place={'C03'},[Questions Complete]={'Yes'},[Place transferred to]-={'C07'}>}Place)/Count({<Place={'C03'}, [Place transferred to]-={'C07'}>}Place) 

What i need to be able to do is show an average of that expression line on my chart.

Any ideas? Thanks, Peter

1 Solution

Accepted Solutions
peterderrington
Contributor

Re: How to calculate an average reference line (in %) for a run chart

Ok, 

I found the solution. We were so close! Just needed to add TOTAL into the expression:

 

AVG(TOTAL AGGR(Count({<Place={'C03'},[Questions Complete]={'Yes'},[Place transferred to]-={'C07'}>}Place)/Count({<Place={'C03'}, [Place transferred to]-={'C07'}>}Place) , [Date]))

 

Thank you so much!

5 Replies
Vegar
Valued Contributor

Re: How to calculate an average reference line (in %) for a run chart

Try something like this:

AVG(AGGR(Count({<Place={'C03'},[Questions Complete]={'Yes'},[Place transferred to]-={'C07'}>}Place)/Count({<Place={'C03'}, [Place transferred to]-={'C07'}>}Place) , [Your X-axis Dimension]))
Please ekskuse my Norglish and Swenglish typos.
peterderrington
Contributor

Re: How to calculate an average reference line (in %) for a run chart

Hi, 

 

Unfortunately it hasn't quite done it. 

The line should be a flat line across the graph showing (in this example) as 33.3%

Average.jpegAny ideas where to go next?

 

Thanks.

peterderrington
Contributor

Re: How to calculate an average reference line (in %) for a run chart

I just double checked some of the properties of the chart and i'd forgotten to alter the position of the axis from Left to Right (to then match the compliance ratio axis), however when i changed it, it then just removed the compliance ratio expression and displayed this expression in exactly the same place.

Vegar
Valued Contributor

Re: How to calculate an average reference line (in %) for a run chart

Do you get the correct total avg value if if you put your original expression into an textbox or chart without dimension?

If so then you could try this expression in your graph.

Count(TOTAL{<
Place={'C03'},
[Questions Complete]={'Yes'},
[Place transferred to]-={'C07'}>}Place)
/
Count(TOTAL{<
Place={'C03'},
[Place transferred to]-={'C07'}>}Place)
Please ekskuse my Norglish and Swenglish typos.
peterderrington
Contributor

Re: How to calculate an average reference line (in %) for a run chart

Ok, 

I found the solution. We were so close! Just needed to add TOTAL into the expression:

 

AVG(TOTAL AGGR(Count({<Place={'C03'},[Questions Complete]={'Yes'},[Place transferred to]-={'C07'}>}Place)/Count({<Place={'C03'}, [Place transferred to]-={'C07'}>}Place) , [Date]))

 

Thank you so much!

Community Browser