Coding Conditional Colours

Paul Scotchford
A free video tutorial from Paul Scotchford
Data Analyst and Qlik Development Specialist @ awari
4.5 instructor rating • 6 courses • 91,674 students

Learn more from the full course

The Complete QlikView Boot Camp

Learn QlikView for Data Analytics - Designer & Developer step by step. Real-World Data Analytics. Real data included.

15:15:50 of on-demand video • Updated April 2020

  • Handle interview questions with ease
  • Start developing solutions for your company as soon as you have finished this course
  • Receive an achievement based certificate. This is based on your test results so you can show the world your skill levels
  • Understand and apply Associative Data Modelling concepts
  • Learn about different Model Schema types
  • Learn to code Joins & understand how they work
  • Learn how the Associative Data Model is stored internally
  • Learn how to load and connect to different data sources (e.g. SQL, Web )
  • Code Scripts for ETL data processing
  • Learn to code calculations using Set Analysis
  • How to code What-If analysis
  • 14 Assignment projects for hands on practice, followed by Quizzes testing your knowledge
  • Learn charting best practices
  • Learn about advanced calculation functions like Aggr() , Rank() , IntervalMatch()
  • Create Charts such as Gauges, Pie, Bar, Combo, Line, Tornado, Heat Maps, Scatter
  • Learn performance tuning for charts and scripting
  • Add actions and conditions to charts and other objects
  • Apply many of your new skills to QlikSense e.g. Modelling, Set Analysis
  • Over 150 Quiz test questions for you to check your learning progress
  • Real world sample data available for download and analysis
  • Install QlikView 12 (and SQLServer express if you want to connect to sample SQL databases)
English [INTRO MUSIC] Report CC errors, thank you. OK welcome back so the user has requested we set some coloring in our top ten sessions by country they have specifically asked us for a green color where the sessions are greater than or equal to 25% they are looking for a yellow where these sessions are falling between 10% and less than 25% and they're looking for that gray if you will for sessions running from 5% to less than 10% and everything else should be just red so how we're going to achieve this we can actually use the if then else construct within click view click view supports if-then-else in expressions and scripting logic basically the if function returns a value depending on whether or not the condition provided with the function evaluates to true so in other words we'll go to test if the session percentages evaluate to true based upon our testing in the code logic so if that was a mouthful don't worry I'm working through it with you what we'll do here is we'll just go into properties and you'll notice on our sessions expression we can just click on the plus bar here which opens up a set of attributes we can change based upon certain conditions that we want to incorporate in this case we're going to look at the background color because we want to set the actual bar color using this attribute and we want to test if the outcome of this calculation falls in any of those three thresholds the users requested however you might think well I've got to write this expression three or four times to make it happen well actually no you don't have to use this expression directly you can actually reference the label in other words this has become an object if you will within the chart itself so what we do here is I'll just take a and I'll open up the expression editor for background color now the first thing I'm going to do is issue an if statement and it's always enclosed in brackets so the first thing I want to test is if the expression that we created sessions percent then you notice it's got a squiggly line we actually have to enclose this in brackets and we want to test if it's equal to or greater than 25 percent so the 25 percent is always or any percentage in this test is represented as a decimal value so what I'm going to issue here is a point two five and what I want to set is a specific color I'm interested in green and green is a function now you notice it had alpha the Alpha determines the depth of color if you will so if it's a 128 then it could be quite light so I'm going to use 128 and just the point here I'm using functions for coloring and if we go into the functions tab we can see that we have a number of functions available to us in the categories and you can see that color is one of them there is a good deal of functions available to you for coloring at this point there's no support for hex values so you have to basically use all the other types that are available to you such as hue saturation light or you can use RGB or you can use the default functions that are available so as you noticed I've used green so let's just hop out is there and if I were to run this now I'll just click apply you can see now that it's detected that the percentage for United States is greater than or equal 25% and as such it's colored the bar let's have a quick look if I change that alpha value to something a little less let's say 64 let's have a look at what the outcome is very very light isn't it so I encourage you to play with these so let's code up the remainder of our threshold colors and what we're going to do here is we're going to issue another if statement that is actually the else component of this first if what we're doing is in fact nesting our if constructs so I'm just going to click if I should say type if sessions and we want to test if it's greater than or equal to 10% in this case so I'm going to say point 1 0 and I want to test that sessions is less than 0.25 if that's the case issue a color code of yellow and set the alpha as 1 to 8 now the alpha just to revisit this the Alpha is just the level of transparency you want to place on that color so if it's 256 then it's deep in other words is full opacity it's going to be a dark yellow the lower down you go in your alpha scales then the lighter that that color becomes in other words it becomes more transparent to the point that it actually doesn't show we've seen that when we looked at the the text objects earlier okay so let's now code the alternate in other words B else for this expression so I'm just going to copy and paste it I'm just indeed slightly it can be very disconcerting writing this code by the way so you might find yourself losing the plot along the way but nonetheless if you keep practicing you'll get there so I want to test for 0.05 greater than or equal to 0.05 and less than 10% and the color we're going to use is dark Gray's we'll just go back to our color and there's dark gray so let's paste it just had it open by default and the alternatives if we don't satisfy any of these conditions we want to display the light red and I'll just make that a 128 as well now we can see that we have some errors here the brackets are showing red this is because we haven't closed the individual tests with a bracket now I did notice an error as I mentioned we had the red brackets which means that the bracket for that particular line needs to be supplied the closing bracket but I actually thought well I shouldn't have actually needed to worry about this one because it should assume that my very last bracket here is the one that applies to this line now what did I do wrong I actually had two brackets I had an error in my color code here so if I remove that no I won't remove it I just want to show you some see how its showing dark gray then the bracket is orange it thinks this section is actually the statement but syntactically that's incorrect so I want to get rid of that extra bracket now if I go back you can see now that that whole line is terminated by closing bracket so let's go and fix the others all we have to do is just add more brackets see how the red turned off and if we just highlight them by moving our cursor over to the bracket you can see that the bracket construct is complete so what we do is we'll click OK and apply and what I expect to see are the colors as per the user requirement to have green yellow dark gray and everything else that fell under those thresholds has turned up red looking good now of course these colors tend to be affected by our filters as you can see things start to move around and the colors in this case a relatively static so it tell a lie there but they do change depending on the lower level values if I select Canada in Germany what will happen in reference to the sessions for the filter of Canada Germany Spain Thailand those color codes have now changed right so hopefully you're comfortable with that I'm just going to clear off all of those filters and what we do in the next lecture is have a quick look at pivot tables I just want to demonstrate the pivot table functionality and then we'll get into an aside after their so see in the next lecture