topic Re: Calculated Background Color in Dimension in QlikView

Calculated Background Color in Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a calculated dimension in a pivot table and I am trying to highlight the 'actual' column if it's greater than the 'projected' column.

The actual calculation is working fine, but I can't get the background color to work properly. I have a feeling I made a stupid mistake but I can't get this last detail working.

Projected dimension is a static value related to the primary dimension Location.

Actual: =aggr(sum(ActualDays),Location)

Background Color: =if((aggr(sum(ActualDays),Location))>ProjectedDays,LightRed())

Desired Result (background instead of text color):

Locations	Projected	Actual	8/1/2017	7/1/2017
Location A	315	410	1	31
Location B	829	619	7	208
Location C	312	1835	1	31

Current State:

Thank you

Re: Calculated Background Color in Dimension

sergio0592 — Tue, 01 Aug 2017 21:47:54 GMT

For me, you don't have to use aggr() in your pivot chart. sum(Actual) and sum(Projected) are enough. And for the text color =if(sum(Actual)>sum(Projected), red())

Re: Calculated Background Color in Dimension

Anonymous — Tue, 01 Aug 2017 22:47:34 GMT

Each location has a static projected days number that is totally separate from other location projections. The actual days also link directly to each location which is why I tried the aggr direction. It just seems strange that the aggr Actual column calculation does not work when using it in an if statement with the background color, but it does work in a calculated dimension.

Thank you

Re: Calculated Background Color in Dimension

vishsaggi — Tue, 01 Aug 2017 23:29:48 GMT

did you try like this?

=if( Sum(aggr(sum(ActualDays),Location)) > ProjectedDays , LightRed() )

Re: Calculated Background Color in Dimension

Wed, 02 Aug 2017 05:34:12 GMT

Hi Philip,

Can u share sample script?

Thank you

Re: Calculated Background Color in Dimension

Wed, 02 Aug 2017 05:49:51 GMT

Dimension->Used Dimension->Select Actual->Click on + Symbol->Background Color-> Write your Expression

Re: Calculated Background Color in Dimension

Wed, 02 Aug 2017 05:50:21 GMT

Re: Calculated Background Color in Dimension

Wed, 02 Aug 2017 06:29:10 GMT

Re: Calculated Background Color in Dimension

oseimuduame — Wed, 04 Jul 2018 08:29:23 GMT

This was very helpful for me.

Thank You for Sharing