without the use of extensions designing a ... - qlik dev...
TRANSCRIPT
Qlik Dev Group Switzerland Event 01Designing a HICHERT-Style P&L waterfall chart in QlikView without the use of extensions---Sebastian Baumhauer, D1 Solutions, 16.11.2016
www.d1-solutions.com
HICHERT / IBCS
2
Disclaimer: I’m not affiliated with HICHERT, but I got a lot of inspiration from visiting one of their courses and from their publicly available information.
An effort to standardizing business communication, with advice on creating effective presentations and data visualizations. www.hichert.com
Source: www.hichert.com
Apple’s P&L Statement Q4 2016
3
Source: www.apple.com
HICHERT approach to P&L statements (-comparison)
4
→ Challenge to self: Create something similar in QlikView (without extensions)
Source: www.hichert.com
5
Result of the implementation in QlikView
Focus today: 3 challenges
6
C) Synchronizing the axes
A)Building a Waterfall chart with guiding lines
B)Creating a Lollipop Chart
7
ABuilding a waterfall chart with guiding lines
Building a waterfall chart with guiding lines1. Create the data foundation
8
Designing a simple waterfall chart is easy
- Create a bar chart with one expression per bar
- Add offset for each bar to match the “end” of the preceding bar
But: Lines can only be drawn across values of a chart dimension
- You cannot draw a line from one expression to another
So: Reflect your waterfall structure in the data so you can use it as a dimension
- The best option is to adjust your data model accordingly (or add a mapping table)
- If you cannot change the data model, it can also be done using a synthetic dimension (avoid this if possible)Pick(ValueLoop(1,12), ‘Revenue’, ‘Cost of revenue’, […])
Building a waterfall chart with guiding lines2. Create the waterfall
9
Create a combo chart, adding a bar expression for your P&L figures
Add the offset to the barsI used chart inter-record functions referencing my main expression Amount
Building a waterfall chart with guiding lines3. Add the lines
10
Add a line expression for your first connecting lineOnce again, I used chart inter-record functions referencing the main expression Amount
Cut off the line by setting it to null outside the desired area (e.g. via dimension index)
Repeat for each of the connecting lines
Building a waterfall chart with guiding linesResult
11
12
BCreating a lollipop chart
Creating a lollipop chart1. Create the sticks and dots
13
Create the sticks (= upper whiskers of a box plot)All other box plot expressions are set to 0
Add the dots to complete the lollipops and color them
Creating a lollipop chart2. Add the numbers
14
Adding the values to the dots just won’t cut it
Values are always on the right
→ Add an expression with bars instead
→ Make the bars transparent
(e.g. ARGB(0,0,0,0) )
15
CSynchronizing axes
Synchronizing axesBackground
16
For a correct display, the scaling of all bars have to be the same
→ How to achieve this?
Synchronizing axes1. Harmonize the axis scales
17
In a variable, calculate the maximum axis extent across all chartsUsually, this would involve an “Aggr” expression over your P&L position dimension. This is an example for the positive maximum of the axis:
Apply the formula to the axis limits of all your chartsIf you know you’ll only have positive income, this step becomes easier :-)
Synchronizing axes2. Adjust chart sizes
18
Fix the layout of your chart by resizing the chart areaCtrl-shift will make the boundaries appear
Resize the charts manually so they show the same bar length for the same value
Synchronizing axesConclusion
19
Excerpt from QlikDevGroup’s Mission Statement
For my X-mas wish list
Qlik, please make axis synchronization across charts more convenient!
20
Sebastian BaumhauerConsultant
D1 Solutions AG | Sihlfeldstrasse 58 | 8003 Zü[email protected] +41 44 435 13 97 | F +41 44 435 10 15 | M +41 76 584 47 34www.d1-solutions.com
Final Remarks
This small project is another example of how QlikView can be tuned (and hacked) to match virtually any business requirement. And while this can be a cool challenge for us Qlik addicts, it also bears the temptation to cross the line to solutions that are hard to maintain. It is difficult to say no to something you know is technically possible, but it’s a good idea to learn how to do so.