Script-Side ColorMix –
Subcategory Color Assignments
This exercise 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.
I have long used the ColorMix wizard in QlikView for heat
map charts. Using this function QlikView
can assign colors to values in your chart based on its position in the range of
all values. This is an immediately
apparent use-case but I wanted to do something a little different.
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:
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.
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.
Activity Color | Top Color | Low Color | |||||||
Activity | R | G | B | RH | GH | BH | RL | GL | BL |
Insanity | 255 | 151 | 16 | 255 | 102 | 0 | 255 | 224 | 185 |
P90X | 84 | 75 | 71 | 84 | 75 | 71 | 233 | 231 | 228 |
Hiking | 212 | 250 | 0 | 98 | 116 | 0 | 230 | 254 | 99 |
Running | 34 | 84 | 158 | 34 | 84 | 158 | 118 | 154 | 207 |
Biking | 130 | 27 | 159 | 130 | 27 | 159 | 186 | 112 | 207 |
Elliptical | 255 | 79 | 79 | 255 | 79 | 79 | 255 | 79 | 79 |
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:
load distinct ActivityKey, Activity as Act, [Activity SubClass] as ActSub, ActSubSort
resident ActivityLog
where not isnull(ActivityKey)
order by ActSubSort;
TempColorMix:
load distinct ActivityKey, Activity as Act, [Activity SubClass] as ActSub, ActSubSort
resident ActivityLog
where not isnull(ActivityKey)
order by ActSubSort;
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:
load ActivityKey, Act, ActSub,
if(Act<>peek('Act'),1,peek('SubRow')+1) as SubRow
resident TempColorMix
order by ActSubSort;
drop table TempColorMix;
// must be done in separate step due to ordering and distinct features.
Temp2ColorMix:
load ActivityKey, Act, ActSub,
if(Act<>peek('Act'),1,peek('SubRow')+1) as SubRow
resident TempColorMix
order by ActSubSort;
drop table 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
left join (Temp2ColorMix)
load min(SubRow) as SubMin, max(SubRow) as SubMax, Act
resident Temp2ColorMix
group by Act;
left join (Temp2ColorMix)
load min(SubRow) as SubMin, max(SubRow) as SubMax, Act
resident Temp2ColorMix
group by Act;
Add in the actual color ranges we had set up in the spreadsheet.
// join in the
guide colors
left join (Temp2ColorMix)
LOAD Activity as Act,
argb(255,R,G,B) as ColorAct,
RH, GH, BH,
RL, GL, BL
FROM Data\Workouts.xlsx(ooxml, embedded labels, header is 1 lines, table is ColorMix);
left join (Temp2ColorMix)
LOAD Activity as Act,
argb(255,R,G,B) as ColorAct,
RH, GH, BH,
RL, GL, BL
FROM Data\Workouts.xlsx(ooxml, embedded labels, header is 1 lines, table is 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:
load ActivityKey, 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*(SubRow-SubMin)/(SubMax-SubMin)-1)*sqrt(fabs((2*(SubRow-SubMin)/(SubMax-SubMin)-1))))/2,rgb(RH,GH,BH),rgb(RL,GL,BL))) as ColorSub // enhanced color dispersion (less collision in the middle spectrum)
resident Temp2ColorMix
order by Act, ActSub;
drop table Temp2ColorMix;
ColorMix:
load ActivityKey, 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*(SubRow-SubMin)/(SubMax-SubMin)-1)*sqrt(fabs((2*(SubRow-SubMin)/(SubMax-SubMin)-1))))/2,rgb(RH,GH,BH),rgb(RL,GL,BL))) as ColorSub // enhanced color dispersion (less collision in the middle spectrum)
resident Temp2ColorMix
order by Act, ActSub;
drop table 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)))
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.
So hopefully this has value for you. 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.
Is this valuable to you?
Then please share.
Do you see other use cases or a better way to do this? Then please comment.
Do you see other use cases or a better way to do this? Then please comment.
No comments:
Post a Comment