Solved: Re: using expression in chart dimension background... - Qlik Community

agigliotti · ‎2016-07-11

! !Hello,

I need to use something like this in my pivot table dim

ension background color:

if( Dim1 = 'Value1', RGB(152, 187, 255) )

i'm using set analysis in my pivot expression.

the question is:

why the above expression also affects the final result of aggregation sum ( {<...>} ) ?

it seems set analysis is not being applied anymore.

Can someone help me to understand why and if it works as expected ?

Currently i'm using the version 12 SR4.

Attached sample app: user = qlik password = view

Many thanks in advance.

Best Regards

Andrea

Gysbert_Wassenaar · ‎2016-07-12

Change

=if( MACROREGIONE_ConRiepilogo = 'TOTALE ITALIA' , $(vColorBackTotIta) )

into

=if( Only( { $(=$(vIfTOTITAODGRO)) } MACROREGIONE_ConRiepilogo) = 'TOTALE ITALIA' , $(vColorBackTotIta) )

Why? Your color expression returns results for more dimension values then your chart expression. You can consider this behavior as the same as what happens when you add that expression as just another chart expression. By adding the same set modifier to all expressions they will all return values for the same dimension values.

talk is cheap, supply exceeds demand

View solution in original post

sunny_talwar · ‎2016-07-11

What exactly is going on? I didn't really get your question, can you elaborate?

Anonymous · ‎2016-07-11

there is no connection between Definition in Dimension Background Color and set Analysis in expressions

I have a Pivot table with a Background Color in one Dimension and two expressions with set Analysis

I does not effect the numbers

can you post a small sample, it might be something different

agigliotti · ‎2016-07-11

SET_01.png shows the result with the if statement.

SET_02.png shows the expected result without the if statement.

agigliotti · ‎2016-07-11

I noted the if expression affect the result only if I compare dimension value, ex if I do something like below:

If( 1=1, RGB(152, 187, 255) ) then it works as expected.

boorgura · ‎2016-07-11

Please try this:

if( Dim1 = 'Value1' and rowno() <> 0, RGB(152, 187, 255) )

Anonymous · ‎2016-07-11

it should work the same if you define your dimensionvalue: if (Dim='Value1',rgb....)

did you suppress when value is null and suppress Zero values?

I use 11.20 sr12

your Image _1 Shows more Dimension values than 2. where does this come from? is this the mistake you mention?

Anil_Babu_Samineni · ‎2016-07-11

Please share sample Pivot table with Expected o/p?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

agigliotti · ‎2016-07-11

I tried but it's the same.

Attached the app with partial data.

I need to disable the "Suppress zero-values".

Yes it's what i'm saying in this thread.

agigliotti · ‎2016-07-11

I did it

check on the first dimension background color expression.

the expected result should be equal to the SET_02.png image

using expression in chart dimension background color