QVNewbie#1 : How to Create QlikView Chart with Both Stacked bars and Line
I have been working on QlikView for many years by now, but my passion for QlikView has never gone down and it is getting increased as the year moves on, I`m still learning new things and Exploring the QlikView World as a newbie. Created this blog to share the information which I know and whatever I have learnt from various sources and from my experience to QlikView lovers.
Though I’m an active participant / reader of the post in Qlik Community but this is my first blog post, I have tried Explaining as much as I could by giving examples, Forgive me if my explanation is not Clear J. For Sure will try working hard to improve skills .
Ok, that`s my intro and let`s get in to the QlikView Newbie Story #1.
I had requirement recently from customers that to build chart which has Stacked bar with Line.I`m going to take sample data to illustrate the Scenario requested.
Here is a requirement which Customer want to see in QV.
Dimensions:
- 1st Dimension as Year,
- 2nd Dimension as Length of the Movie
Expressions:
- 1st Expression as Count of Movie (This Expression Should be Stacked based on 2nd Dimension ‘Length of the Movie’)
- 2nd Expression as Average Rating of the movie.
By Year, Count of Movie has to be stacked based on the Second dimension and Average of Rating by Line.It is pretty impossible to get the scenario working by using the combo chart with two dimensions, one expression as Bar and one Expression as line in QlikView (As far as I know :D). So we need to create chart with multiple Expression for second dimension.
Let’s See how do we achieve this in QlikView.
Step 1: Right Click on the Sheet >> go to New Sheet Object >> Chose Chart…
Step 2: Chose combo chart 
from the list of available chart for our requirement.
Step 3: Say next and Chose your dimension, though it may look like we need two dimensional chart to achieve our requirement but really not the case, so we are going to choose only one dimension. If we choose the second dimension as length of the movie, stacked bar and Line will not work together, So Let’s Choose only one Dimension from here as shown below.
Step 4: Say next to work on the expressions. Our requirement is to show the count of Movie by its length as a stacked bar and line as the average rating of the movie.
Distinct Value in my length of the movie (LENGTH_RANGE) column is 5 rows (i.e.) my Second dimension.
So i`ll be creating 5 expressions to get the count of movie for each of my Value in ‘Length Range’ Column as shown below.
Expression 1 - Label this as ‘< 1hr ’ : Sum ({<[Length Range] = {"< 1hr"}>} FilmCount)
Expression 2 - Label this as ‘1 to 1.5hrs ’ : Sum ({<[Length Range] = {"1 to 1.5hrs"}>} FilmCount)
Expression 3 - Label this as ‘1.5 to 2hrs ’ : Sum ({<[Length Range] = {"1.5 to 2hrs"}>} FilmCount)
Expression 4 - Label this as ‘2 to 2.5hrs ’ : Sum ({<[Length Range] = {"2 to 2.5hrs"}>} FilmCount)
Expression 5 - Label this as ‘3hrs+ ’ : Sum ({<[Length Range] = {"3hrs+"}>} FilmCount)
Expression Tab :
Once we created the Expression then Change Expressions ‘Display Options ‘ to Bar, default will be Line
Step 5 : Once the above steps are performed, then Go to ‘Style’ tab and Change your ‘Subtype’ to ‘Stacked’
Step 6: Say ok to see your stacked chart, Very Excited to See your Chart ;)?? We are still pending with one more step to add our line but still let’s go back and see what ever we have done till now makes sense.
Will see a Stacked bar chart as below.
Step 7: Now our Stacked bar is ready, so will continue adding the expression for line in our chart. Just Go back and add one more expression for Average of Rating
Expression #6 - Label this as ‘Avg Rating’ : Avg (Rating)
Your final Expression tab should be something like below.
Change the expression #6 (Avg Rating) to Line in Display Option.
Step 8: Say ok and come out, you`d see a chart with Stacked bar with line.
Only drawback of the above approach is that, if new value list is getting added in my length of Range column, QlikView will not understand to add that new value list as new expression, we will have to add those manually. Apart from that Graph should work without any issue.
Hope this post helps atleast few Qlikers on this QlikView World and I will be happy to correct if any of my above information is misleading or wrong.
Sample Application is attached with this post, have a look and let me know your thoughts!!
Thanks for reading the post and Happy Qliking!!!
