topic Re: Change Background color of a dimension in Qliksense Pivot table in Visualization and Usability

Change Background color of a dimension in Qliksense Pivot table

Krish — Wed, 01 Mar 2023 13:11:55 GMT

Hi,

I have a dimension in pivot table which needs to be have different color codes based on the dimension value.

Eg:

If(Country = 'US','RGB(255,0,0)', If(Country = 'EU','RGB(0,255,0,)',RGB(0,0,255)')

The Background color in dimension works only for 1 color code and does not work for multiple if conditions. How can this be achieved.

Re: Change Background color of a dimension in Qliksense Pivot table

ajaykakkar93 — Thu, 02 Mar 2023 05:13:29 GMT

Hi,

I used Pick Wildmatch , below is the code for the same wich i added for both dimension Background color expression, You can use # color also example: #ccc etc...

pick(WildMatch(Dim1,'A','B','C','D'),'red','blue','green','#ccc')

Re: Change Background color of a dimension in Qliksense Pivot table

henrikalmen — Thu, 02 Mar 2023 06:54:30 GMT

It should workfor multiple conditions. But I see one issue in your expression: you should not have single qoutes around the rgb function. Do it like this: if(country='US', rgb(255,0,0), if(...

You could also use pick and match as Ajaykakkar93 suggested, but it doesn't have a fallback for "all other countries" like the if statement does.

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 10:01:19 GMT

@henrikalmen & @ajaykakkar93 My condition for dimension coloring is :

if(num(Score, '#.##')>=3.30,'#00FF00',

if(num(Score, '#.##')>=2.30,'#FFFF00','#FF0000'

))

But this is not working. It always goes to else condition.

Re: Change Background color of a dimension in Qliksense Pivot table

neerajthakur — Thu, 02 Mar 2023 10:09:22 GMT

if(num(Score, '#.##')>=3.30 and num(Score, '#.##')<2.30,'#00FF00',

if(num(Score, '#.##')>=2.30 and num(Score, '#.##')<3.30,'#FFFF00','#FF0000'

))

Try this

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 10:29:00 GMT

@neerajthakur I tried it, but it just doesn't work. I tried different combination of numbers like :

if(num(Risk_Score, '#.##')=3.30 ,'#00FF00',

if(num(Risk_Score, '#.##')=1.70 ,'#FFFF00'

))

Re: Change Background color of a dimension in Qliksense Pivot table

henrikalmen — Thu, 02 Mar 2023 10:34:15 GMT

Try this:

if(num(num#(Risk_Score), '#.##')=3.30 ,'#00FF00',
if(num(num#(Risk_Score), '#.##')=1.70 ,'#FFFF00'
))

Re: Change Background color of a dimension in Qliksense Pivot table

neerajthakur — Thu, 02 Mar 2023 11:01:45 GMT

Hi try this and let me know the output where value is 3.30

if(Risk_Score=3.30,red(),blue())

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 11:00:57 GMT

@neerajthakur Its not working either. I tried

1) =if([Workload.Risk_Score]=2.40,red,blue)

This says "Error in expression".

2) =if([Workload.Risk_Score]=2.40,'#00FF00','#FFFF00')

It always goes to Else part.

Re: Change Background color of a dimension in Qliksense Pivot table

neerajthakur — Thu, 02 Mar 2023 11:10:28 GMT

Try with value which you have like 3.30 and 1.70,1.20 and use this red(),blue(),

The field you are using for coloring is it dimension or measure, if measure what's the expression.

It might be you are using some calculation for measure value and using different logic in color expression.

I can't see the search icon and hoping it's measure only. Share your measure expression to help better.

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 11:40:01 GMT

@neerajthakur I did try using the conditions as you mentioned.

=if(num([Workload.Risk_Score],'#.##')=2.40,red(),blue())

It goes to Else Part.

Also, it is a dimension and I am not using any other calculation for this dimension.

Re: Change Background color of a dimension in Qliksense Pivot table

neerajthakur — Thu, 02 Mar 2023 12:29:50 GMT

Don't know what's happening would suggest you to try in a separate app using inline load.

Does all the values end with 0 in your case then try this expression

=if(num([Workload.Risk_Score],'#.#0')=2.40,red(),blue())

Share screenshot from Model Viewer of that field from Preview Pane, I want to see the Tags there, if it's not numeric then try making it using Num() or Num#()

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 12:54:43 GMT

Thanks for you response.

Does Vizlib not support this feature?

Re: Change Background color of a dimension in Qliksense Pivot table

Clever_Anjos — Thu, 02 Mar 2023 13:07:45 GMT

I´d rather use a pick

pick(Match(Country ,'US','EU','LATAM'),
RGB(255,0,0),
RGB(0,255,0),
RGB(0,0,255),
)

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Thu, 02 Mar 2023 13:20:02 GMT

But Pick might work when the value is 2.4, 1.5 etc., but my requirement is that

if Score is GE 3.5 --> Green

if Score is between 2.5 and 3.4 --> Blue

if Score LE 2.4 --> Red

Re: Change Background color of a dimension in Qliksense Pivot table

ajaykakkar93 — Fri, 03 Mar 2023 04:00:55 GMT

hi,

i guess column => [Workload.Risk_Score] is added as a measure i.e =sum([Workload.Risk_Score]) ?
if that so then try this

=if(column(NUMBER)=2.40,red(),blue())

make sure to write the correct column i.e column(1) or column(2)

Re: Change Background color of a dimension in Qliksense Pivot table

vinieme12 — Fri, 03 Mar 2023 06:04:37 GMT

format HEX codes as below

=if([Workload.Risk_Score]=2.40, Num#('#00FF00','HEX'), NUM#('#FFFF00','HEX'))

Re: Change Background color of a dimension in Qliksense Pivot table

Krish — Fri, 03 Mar 2023 17:02:27 GMT

Hi @vinieme12 & @ajaykakkar93 . I tried both. It just doesn't work. Is it a limitation with Vizlib. This works with normal pivot table, but not with Vizlib.

Re: Change Background color of a dimension in Qliksense Pivot table

Clever_Anjos — Fri, 03 Mar 2023 18:25:27 GMT

Sorry, got confused by your example

Re: Change Background color of a dimension in Qliksense Pivot table

neerajthakur — Sat, 04 Mar 2023 08:14:13 GMT

It might, would suggest to raise issue with VizLib support.