topic Re: Background Color Coding for a Calculated Dimension in a Pivot Chart in QlikView

Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Sat, 16 Nov 2024 19:28:59 GMT

Community.

I am puzzled by my situation.
I have a calculated dimension and I want the background colored based on the result of that calculated dimension.

But it is only coloring a few of the available results in the table.

My calculated dimension expression is:

=Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])

My expression background color expression is:

=If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='G',RGB(146,208,80),

If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='Y',RGB(255,255,0),

If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='R',RGB(255,0,0))))

Has anybody else experienced this and what have you found as a solution?

Thanks.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 17:11:01 GMT

Do you get a single value of rating on each row or do you sometimes have multiple values as well? like R,G?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 17:16:06 GMT

The possible values are G, R/G, NR, Y/G, Y, R, G/Y, R/Y, Y/R, G/R

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 17:25:26 GMT

What would you want R/G to be colored as?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 17:27:22 GMT

I don't think we can do gradients in Qlik Pivot Charts, so the combination colors can be the lower case color. So R/G would be Red, Y/G would be Yellow etc.

Mainly I really just want to understand why the others are not being colored lol.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 17:30:18 GMT

What exactly do you mean by Others?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 17:33:39 GMT

when I set it up and added the expression to the background color portion of the Calculated Dimension, in the results it only colors three items and leaves the other blank.

I have multiple 'G' results and it will only color 1 and same for 'R' and 'Y'.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 18:37:35 GMT

Can you try this

=If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='G',RGB(146,208,80), If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='Y',RGB(255,255,0), If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='R',RGB(255,0,0))))

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 18:46:36 GMT

So it colored a few more but not all! I am getting closer! Is it a glitch??

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 19:04:25 GMT

May be share a sample so that we can check it out.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 19:19:23 GMT

Here is a rudimentary one outlining really how I would want this to show.

Hope it helps.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 19:22:17 GMT

Can you pin point where we can see the issue here.... also, the expression used here is different then expression you are using... can you share how this links to your actual dashboard?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 19:27:24 GMT

Unfortunately it would be extremely challenging to recreate this issue since I cannot share my actual data for my dashboard.

However, the reason I am using Set Analysis is because I have multiple items with multiple categories and in each one classified differently.

Because the dashboard is selecting only a few items to color is why I am thinking that there might be a Qlik issue vs. an expression issue?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Tue, 10 Dec 2019 19:33:26 GMT

I am not sure how to help if I don't know the issue... May be someone else might be able to help.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Tue, 10 Dec 2019 19:35:05 GMT

Thats what I feared.

Thanks though Sunny.

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Wed, 11 Dec 2019 21:57:08 GMT

Hi Sunny,

Took me a little while, but I have attached a sample of what I would like.

In the sample, you will see that there is no color background for rating, but when you click on one of the Models, it shows up.

Any ideas?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Thu, 12 Dec 2019 00:44:40 GMT

Try this

=If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'G', Green(), If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'Y', Yellow(), If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'R', Red(), White())))

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Thu, 12 Dec 2019 11:06:15 GMT

It works.

So can you help explain what the expression is doing?

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

sunny_talwar — Thu, 12 Dec 2019 11:41:13 GMT

Instead of doing Aggr(, Model) I just used the TOTAL <Model> without Aggr because your chart already had Model, Rating as the dimensions... To tell you the truth I am not sure why Aggr(, Model) didn't work... I would have thought it to have worked also... but I recreated the same idea using TOTAL <Model>

Re: Background Color Coding for a Calculated Dimension in a Pivot Chart

ZoeM — Thu, 12 Dec 2019 11:43:18 GMT

Can we use something else vs <Total>?

Because in my dashboard <Total> is ignoring all other filters and showing more values form the expression side of the pivot chart. .