Assign Colors in QlikView Script – Let’s Do Something Cool with Color

QlikView Color Script - Featured Image - Donut Chart for Workout activity

What are we Trying to accomplish?

In this exercise, you will learn to create a list of RGB colors and assign them to dimensional values in our QlikView script. This came from something I did for my personal use, but I think the principal can be applied to business cases as well.  You can download the example here:  Lets Do Something Cool with Color.zip
I have a regular fitness program where I mix up different types of activities throughout the week.  In addition to the main activities, I want to be able to visually separate sub activities from each other while still maintaining them as activity groups.  Here are screen shots of the Activity and the Activity SubClass broken out to show you what I mean:

 

QlikView Script Color - Featured Image - Donut Chart for Workout activity
Left – By Activity                                Right – By Activity SubClass

 

So my P90X programs will always be some shade of gray.  My Insanity programs will all be some shade of Orange, etc.  The front-side ColorMix function is great, but it would be very difficult to assign these in the front-end especially considering the grouped dimension and varying numbers of SubClass groups as time goes on.
The idea is that when only the Activities are shown, I want to explicitly assign the main colors.  But when we break these out into Activity SubClass, I want to dynamically assign a shade of that Activity color to each of the Activity SubClass values within it.  I realized quickly that the best way to do this was to utilize ColorMix in the script.

How do we do it?

The first step is to decide what colors should be assigned to each category.  Create a spreadsheet and give each Activity an RGB value.  You must also decide what will be the range of colors that will be used to create the Activity SubClass colors.  In this case, the Activity list will need to be maintained, but the SubClass values can vary because the actual RGB values for each distinct SubClass will be assigned at reload time. If you want some help creating the incremental colors, see this article.
  
Activity ColorTop ColorLow Color
ActivityRGBRHGHBHRLGLBL
Insanity255151162551020255224185
P90X847571847571233231228
Hiking212250098116023025499
Running34841583484158118154207
Biking1302715913027159186112207
Elliptical255797925579792557979
Next you must create the script that will load these up and create the structure that will facilitate the ColorMix operation. 
Create a table with the distinct Activity SubClasses creating a key.
//  load distinct keys
TempColorMix:
loaddistinctActivityKey, ActivityasAct, [Activity SubClass]asActSub, ActSubSort
resident ActivityLog
wherenotisnull(ActivityKey)
orderbyActSubSort;
Create a counter row that resets for each major Activity.
//  add the subrow row numbers for each activity
//  must be done in separate step due to ordering and distinct features.
Temp2ColorMix:
loadActivityKey, Act, ActSub,
if(Act<>peek(‘Act’),1,peek(‘SubRow’)+1) asSubRow
resident TempColorMix
orderbyActSubSort;
droptable TempColorMix;
Now find the low and high value for each activity.  This will give the ColorMix function the ranges to create colors for.
//  find the max and min row number for each activity
leftjoin (Temp2ColorMix)
loadmin(SubRow) asSubMin, max(SubRow) asSubMax, Act
resident Temp2ColorMix
groupbyAct;

Add in the actual color ranges we had set up in the spreadsheet.
//  join in the guide colors
leftjoin (Temp2ColorMix)
LOADActivityasAct,
    
argb(255,R,G,B) asColorAct,
    
RH, GH, BH,
    
RL, GL, BL
FROM DataWorkouts.xlsx(ooxml, embeddedlabels, headeris 1 lines, tableis ColorMix);
Note that you can use a standard color dispersion or an enhanced color dispersion that results in a more distinguishable set of colors.  I put both examples in the sample script and just commented out the standard one.
//  now we can get the color for each Activity SubClass and create final table
ColorMix:
loadActivityKey, ColorAct,
   
//if(SubMax=1,ColorAct,colormix1((SubRow-SubMin)/(SubMax-SubMin),rgb(RH,GH,BH),rgb(RL,GL,BL))) as ColorSub//  standard color dispersion
    if(SubMax=1,ColorAct,colormix1((1+sign(2*(SubRowSubMin)/(SubMaxSubMin)-1)*sqrt(fabs((2*(SubRowSubMin)/(SubMaxSubMin)-1))))/2,rgb(RH,GH,BH),rgb(RL,GL,BL))) asColorSub//  enhanced color dispersion (less collision in the middle spectrum)
resident Temp2ColorMix
orderbyAct, ActSub;
droptable Temp2ColorMix;
 
After reloading, we can focus on visualization. Create a variable expression to assign our colors.  We used $(vColorPick).
 
if(getcurrentfield(ActivityGroup)=’Activity’,ColorAct,
   if(count(distinct
[Activity SubClass])=1,ColorSub,rgb(154,151,170)))

This variable can be called in the Background Color attribute for the expression in any chart.  If you need help with Background Colors in Expression Attributes reference this article.  We added an else statement to take care of any values we neglected to assign in our spreadsheet.

Final Thoughts

So that is how I assigned QlikView colors in the script.  I have posted the spreadsheet and qvw here.  Feel free to use it to learn more about this method OR obviously you could use it to keep your own workout log.
There is a nice article on QlikFix that also describes how to assign specific persistent colors to dimensions.

Do you see other use cases or a better way to do this? 

Comments on this entry are closed.

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons