Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I want to assign a specific color for each category of [Agent supergroup] in my drilldown master item, much like asked here.
I figured that I could color my graph by expression, with an expression (or, as suggested, an expression stored as a second master item) starting like this
=If(GetSelectedCount([Agent supergroup])<>1,
Pick(Match([Agent supergroup], 'Hospitals', 'Private laboratories', 'Private practices', 'Other'), '#006580', '#C8C7A9', '#AC4D58', '#99CFCD'), ...)Without further colors instead of the '...', all subcategories will be colored gray. I guess that I could add more Ifs for [Agent group] and [Agent], but since
- I do not know if the values in the underlying table will change and
- I care only about color consistency across different graphs,
I just want to assign [Agent group] a color by its position in the list of possible values:
Concat({$<[Agent group]=>} DISTINCT [Agent group], ',')So I constructed an expression around this to get a color number between 1 and 12 and then added Pick() as follows:
If(GetSelectedCount([Agent group])<>1,
Pick(Mod(Len(KeepChar(Left(Concat({$<[Agent group]=>} DISTINCT [Agent group], ','), Index(Concat({$<[Agent group]=>} DISTINCT [Agent group], ','), [Agent group])-1), ',')), 12)+1,
'#006580', '#C8C7A9', '#AC4D58', '#99CFCD', '#E1DAD5', '#83AF9B', '#E0BD8D', '#8A85C6', '#10CFC9', '#A16090', '#87205D', '#C4CFDA'), ... )(These are the 12 default Qlik Sense colors.)
My test-KPI shows the correct color number, but the categories of [Agent group] in the graph are still one and the same color.
Do you have a hint?
Hi @AlejandroVm ,
Yes, all I want is consistent colors across graphs. This is easy to set for a master item for one single dimension, but not for drill-down dimensions. I found a workaround (below), but would prefer to use some formula to color the drill-down dimension by expression.
This example has inconsistent colors:
A workaround I came up with this morning is using several charts inside a container, one for each hierarchy level, formatted so that it appears to be one chart only to the viewer:
- Create master items for each single level of the drill-down dimension ([Agent supergroup], [Agent group], [Agent]),
- edit each master item once more and and assign custom value colors in the settings dialog (I use the default 12-color palette),
- inside a new container chart, create one chart per master item (donuts, in my example) by copy-pasting the chart that is already there and adjusting the variables,
- for each of these, set the show condition in the settings of the container so the relevant graph is shown (or hidden) whenever only one item is selected, =GetSelectedCount([Agent supergroup])=1
- in each chart, set the sorting by the relevant master item first (to be done in the charts of other containers as well to be consistent) and optionally hide the legend title,
- repeat for all other charts of the sheet.
Here's my container of donut charts (still showing tabs):
I still hope there's a simpler, more general solution applying to any drill-down dimension.
Hey,
I’ve been working on your issue, and here’s what I came up with to get those consistent colors across different graphs for your drill-down dimension.
Key Points:
- Fixed Colors for
Agent supergroup: EachAgent supergroupcategory has a fixed color that stays the same across different types of graphs. - Dynamic Colors for
Agent groupandAgent: The subcategoriesAgent groupandAgenthave dynamic colors based on their position in the list of values. So, values in the same position across different categories get the same color. I hope I understood this part correctly.
Expression:=If(GetSelectedCount([Agent supergroup])<>1, // Fixed colors for Agent supergroup Pick(Match([Agent supergroup], 'Hospitals', 'Private laboratories', 'Private practices', 'Other'), '#006580', '#C8C7A9', '#AC4D58', '#99CFCD'), // Dynamic colors for Agent group If(GetSelectedCount([Agent group])<>1, Pick(Mod(RowNo(TOTAL), 12)+1, '#006580', '#C8C7A9', '#AC4D58', '#99CFCD', '#E1DAD5', '#83AF9B', '#E0BD8D', '#8A85C6', '#10CFC9', '#A16090', '#87205D', '#C4CFDA'), // Dynamic colors for Agent Pick(Mod(RowNo(TOTAL), 12)+1, '#006580', '#C8C7A9', '#AC4D58', '#99CFCD', '#E1DAD5', '#83AF9B', '#E0BD8D', '#8A85C6', '#10CFC9', '#A16090', '#87205D', '#C4CFDA') ) )
Let me know if this works for you!
Best regards,
Alex
Hi @AlejandroVm,
Thanks a lot for your time! The first part of the color expression reliably sets the color for the first dimension of the drill-down master item ([Agent supergroup]) across all charts, but not always for the second and third dimension ([Agent group] and [Agent]). In the examples you took, this wouldn't appear.
Screenshot for the first dimension of the drill-down:
When selecting one [Agent supergroup], the colors for [Agent group] should be the same, but my stacked bar chart uses all 12 colors of the scale (one stacked bar per Lab, colored by drill-down).
Screenshot for the second dimension of the drill-down:
This is why I thought to use something else than RowNo(TOTAL) as <position> within the expression Pick(Mod(<position>, 12)+1, <colors>). But what could one use?
I was thinking of using the position of the current [Agent group] within the list of all DISTINCT [Agent group]. This does currently only work within KPIs.
- In the list of all values of [Agent group] currently available: Concat({$<[Agent group]=>} DISTINCT [Agent group], ',')
- Index(<value list>, [Agent group]) gives the position of the currently selected [Agent group], if there's only one, and 0 else.
- To further beautify, I thought of using Len(KeepChar(Left(<value-list>, Index(...)-1), ',')). This returns integers between 0 and the number of categories, and this is nice since Qlik Sense would use the colors of the color scale in the intended order. I think it is optimized for the color-blind, so order matters.
The following expressions works fine in a KPI to show which [Agent supergroup] or [Agent group] was clicked, but they do not work in my color expression:
=Len(KeepChar(Left(Concat({$<[Agent supergroup]=>} DISTINCT [Agent supergroup], ','), Index(Concat({$<[Agent supergroup]=>} DISTINCT [Agent supergroup], ','), [Agent supergroup])-1), ','))
=Len(KeepChar(Left(Concat({$<[Agent group]=>} DISTINCT [Agent group], ','), Index(Concat({$<[Agent group]=>} DISTINCT [Agent group], ','), [Agent group])-1), ','))
What else could I use to replace RowNo(TOTAL)? Thanks!
Sorry for not having been able to be clearer in my explanation! I'll try again, recapitulating a bit:
I have a number of fields forming a hierarchy, and I created a drill-down master item as shown below. All charts on my sheet should show consistent colors. The color itself is not really important, but it would be best to use Qlik Sense's (probably color-blind-friendly) 12-color scale.
Let's generate some data to illustrate the problem:
// Generate random data
// The names and number of hierarchy levels may come from the real data.
// Here, top hierarchy level [Agent supergroup] has 4 categories, the
// otheres are named [Agent group] and [Agent].
// Data are not evenly distributed across labs.
data:
Load
RowNo() AS OrderID,
Pick(i+1, 'Hospitals', 'Private labs', 'Private practices', 'Other') AS [Agent supergroup],
'Group_' & Num(j+1) AS [Agent group],
'Agent_' & Num(k+1) AS [Agent],
'Lab_' & Num(Floor((k+10*1.1*(j+6*1.2*i))/(9+10*1.1*(5+6*1.2*3))*10.3)+1) AS Lab;
Load
Floor(4*Rand()) AS i,
Floor(6*Rand()) AS j,
Floor(10*Rand()) AS k
AutoGenerate(1000);
Initially, using the default settings, colors are not consistent across charts:
One fix might be to color the charts 'by expression'.
For the first hierarchy level, this is easy. Just use =Pick(Match([Agent Supergroup], <values>), <colors>).
This is the expression proposed by @AlejandroVm and modified by @Rohan based on my initial idea for all three hierarchy levels :
=If(GetPossibleCount([Agent supergroup])<>1,
// Fixed colors for Agent supergroup
Pick(Match([Agent supergroup], 'Hospitals', 'Private labs', 'Private practices', 'Other'),
'#006580', '#C8C7A9', '#AC4D58', '#99CFCD'),
// Dynamic colors for Agent group
If(GetPossibleCount([Agent group])<>1,
Pick(Mod(RowNo(TOTAL), 12)+1,
'#006580', '#C8C7A9', '#AC4D58', '#99CFCD', '#E1DAD5', '#83AF9B', '#E0BD8D', '#8A85C6', '#10CFC9', '#A16090', '#87205D', '#C4CFDA'),
// Dynamic colors for Agent
Pick(Mod(RowNo(TOTAL), 12)+1,
'#006580', '#C8C7A9', '#AC4D58', '#99CFCD', '#E1DAD5', '#83AF9B', '#E0BD8D', '#8A85C6', '#10CFC9', '#A16090', '#87205D', '#C4CFDA')
)
)
Since values of [Agent supergroup] are known and fixed, we can use Pick(Match(...), ...) to select a color in the expression; this is not the case for the other hierarchy levels, so a @AlejandroVm proposed to use Pick(Mod(RowNo(TOTAL),12)+1, ...) instead.
Without selection, colors for [Agent supergroup] are consistent everywhere:
But when [Agent supergroup] = 'Hospitals' is selected and the next hierarchy level ([Agent group]) is shown, the colors for that level are inconsistent across charts and even within the bar chart:
In the current example, Group_3 has different colors in Lab_1 and Lab_2, whereas it should be the same as in the pie chart:
So it seems that I need to find some clever replacement for RowNo(TOTAL) to Pick() a color. That solution could be generalized to any level of hierarchy.
Does this better illustrate my point?
