Bullet charts (well sort of) using a stack chart

    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.

     

    Qlikview Watermark Bar Charts (A Bit Like Bullet Charts..But Not Quite) | QVDesign

     

    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:

    bullet chart.png

    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.

    test:

    LOAD

      DATE#(Month,'MMM-YY') as Month,

      Target,

      Actual

    INLINE [

    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.