I'm wondering if there is a better way to create the visualization I am about to describe. Hopefully one of you has already figured this out!
I have a set of measures that I need to display in a tabular format. For each measure I need to display the measure value for the person being scored along with the aggregate score of their peers and a benchmark that they are being compared against.
Currently the way I am doing this is I am creating each measure as a dimension. I then create three expressions using set analysis to control which measures display in my chart and to define the peer groups:
- Expression 1: self score
- Expression 2: peer group score
- Expression 3: benchmark
Some of these measures are rolling-N month measures, others are ratios of two measures and others are various permutations of multiple measures (ex: Measure 1 + Measure 2 / Measure 3 * Measure 4 ...). In order to get these trickier measures to display I have to create a row in my measure dimension for these measures and then pre-calculate these in my load script. To make matters worse some measures are just a sum of the numerator, others are a ratio of the numerator and denominator and others are an average of the numerator. This kind of defeats the unique ability of QlikView to aggregate (correctly) on the fly based on user selections.
Is there a better way to create a chart like this? I have attached an Excel document if it explains my scenario better. The only other option I came up with was to create one chart for each measure, using a calculated dimension to display plain text as the measure name and then layering the charts on top of each other to present the illusion of a single chart. This works fine until you run into a person that doesn't have values for all of the possible measures. Some measures are specific to the department that the person is working in so I cannot guarantee that each person will always have the same ten measures displayed.