One of the major gaps in QlikView is a lack of bullet charts and/or watermark charts. The feature that these charts share is the ability to quickly compare the actual value to another value spread across all values of a dimension. This blogpost does a much better job of explaining the challenge and proposing a solution.
The solution presented in this document is simpler (read: less clever) but should help you to implement your own version.
This is the finished chart:
How is it done:
1) The chart is based on a very common dataset.
- A Dimension
- An Actual value
- A Target value
In this case I created an inline table with a series of months and a actual and target value for each month. Nothing clever.
DATE#(Month,'MMM-YY') as Month,
Month, Target, Actual
Jan-14, 1000, 900
Feb-14, 1000, 1100
Mar-14, 1000, 500
Apr-14, 700, 800
May-14, 400, 300
2) The chart is a basic stack chart. The cunning bit is when you get to the expression.
- First I added the Month dimension.
- In Styles I switched to a horizontal view (just my preference)
- in the colour palette I set colour 3 to be black.
3) Now the expressions
- Expression 1 (I called it 'first bar). The value is set to the smaller of sum(Actual) or sum(Target) using a simple if statement.
- The background colour is set to either COLOR(1) or COLOR(2) depending on if the Actual is more than the Target
- Expression 2 (I called it 'second bar'). If the target is smaller than the actual then this expression returns a fixed bar*, or it is returns the 'gap' between a smaller actual and the target.
- The background colour is set to either COLOR(3) for the bar or WHITE() for the gap.
- Expression 3 (I called it 'third bar'). If the target is larget than the actual then this expression returns a fixed bar*, or it returns the remainder of the Actual value after subtracting the Target and the fixed bar.
- Expression 4 (I dont call it anything). Is used as a 'text as pop-up' onlyto control the popup text users see.
That's it. Just a bit of simple IF...THEN...ELSE controlling the bar size and colours to achieve this effect.
I have attached a copy of my example doc for you to look at.
* the only bit that is not so simple is the formula to create a fixed width bar. This relies on a MAX(ALL(AGGR formula to return a fixed bar relative to the Actual values shown.
Hope you like it.
Comments and feedback always welcome.