Qlik Community

QlikView Documents

Documents for QlikView related information.

Bullet charts (well sort of) using a stack chart

dfoster9
Valued Contributor

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.

Attachments
Comments
vadim_grab
New Contributor III

Dear, David. Can I ask you to post more detail about used expressions and chart tuning? Now I working on Sales managers Dashboard and have some similar task  (Bullet Graph Design by Stephen Few). But can't find any realization details...

Thanks a lot.

VG

dfoster9
Valued Contributor

Hi Vadim

Happy to help.

What are you particularly struggling with?

vadim_grab
New Contributor III
Not applicable

Hi,

I could make this chart using your file, really helpful ! can you please tell me how can I add labels to the bar and gantt?

Version history
Revision #:
1 of 1
Last update:
‎06-02-2014 10:08 AM
Updated by: