3 Replies Latest reply: Mar 2, 2016 4:13 AM by Marcus Sommer RSS

    Line chart with line showing average at each point

    Declan Burke

      Hi everyone,

       

      Sorry I'm new to QlikView and the forums, so I apologise if this is a silly question or in the wrong category or has been answered elsewhere - but if so I would really appreciate being redirected


      So I'm wondering if it's possible to create a line chart (with many lines of data), but then add a single line which shows the average/mean of all the other lines at that point on the x-axis.


      So to give an example, say we have a fitness/training course, where competitors take turns to run through a course with timed checkpoints along the way. The course has a "guide" time for each checkpoint, ie the competitors are expected to reach the first checkpoint 60 seconds after they started the course, expected to reach the second checkpoint 90 seconds after they started, etc.

      So then say we have data with three fields:

      • Competitor (competitor name)
      • Checkpoint (checkpoint number)
      • TimeDiff (difference between the competitor's actual time at a checkpoint and the "guide" time to reach the checkpoint)

       

      I can easily create a line chart which has Checkpoint along the bottom (x-axis) and TimeDiff along the side (y-axis), with each Competitor as an individual line/series on the graph (by using Checkpoint and Competitor as dimensions and TimeDiff as the expression), like so:

      (I've presented the lines without the connecting lines and just with cross symbols)

      Fitness course chart 1.png

       

      However, I then want to add another line which shows the average/mean of all the competitor's time to reach that individual checkpoint. I can create this as its own chart (by using Checkpoint as dimensions and Avg(TimeDiff) as the expression), like so:

      Fitness course chart 2.png

       

      However, I would like them on the same chart. So basically I would like one chart to look like the two charts overlaid, like so:

      Fitness course chart 3.png

      I would like a solution that works within the chart (ie calculates the average on the fly) and for any numbers of data series, as the idea would be to use a much larger data set (with many more competitors and other fields to filter on, such as age, gender, etc).

      Sorry if this is a basic question, I believe QlikView should be able to do this but I've tried fiddling with the dimensions and expressions and can't get it all together on the one chart.


      I've attached my QlikView file (with both example data and charts) and the example data on its own (csv file).


      Thanks heaps,

      Declan