Color by expression/measure Qlik Sense

[g23]

Hello all, 

I'm a beginner of Qlik Sense and I need to write the color expression in a pivot table for this field:

Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])

I tried to write the expression as following, but it does not work:

= if(Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])<0.02, rgb(51,51,255),

if(0.02<Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])<0.05,rgb(0,51,102)),

if(0.05<Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead]) <0.08,rgb(0,153,153),rgb(255,250,230)))

Can someone help me with this issue? 

Thanks

[Sameer9585]

Create a variable as vCal=Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])

and use this formula

if($(=vCal)<0.02,rgb(51,51,255),
if(0.02<$(=vCal)<0.05,rgb(0,51,102),
if(0.05<$(=vCal)<0.08,rgb(0,153,153),rgb(255,250,230))))

[g23]

I tried, but it's not working. 

I created the variable : Conversion rates dirette = Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])

Then I write down the color expression as follow:

if($(=Conversion rates dirette)<0.02,rgb(51,51,255),
if(0.02<$(=Conversion rates dirette)<0.05,rgb(0,51,102),
if(0.05<$(=Conversion rates dirette)<0.08,rgb(0,153,153),rgb(255,250,230))))

but unfortunately it's not doing anything

[Sameer9585]

Try this?

if($(="Conversion rates dirette")<0.02,rgb(51,51,255),
if(0.02<$(="Conversion rates dirette")<0.05,rgb(0,51,102),
if(0.05<$(="Conversion rates dirette")<0.08,rgb(0,153,153),rgb(255,250,230))))

[Sameer9585]

Else try this?

= if(Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])<0.02, rgb(51,51,255),

if(0.02<Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead])<0.05,rgb(0,51,102),

if(0.05<Count({$<[Tipo Conversione]={D}>}[ID Polizza]) / Count(distinct{$<flg_first_policy>}[ID Lead]) <0.08,rgb(0,153,153),rgb(255,250,230))))

[g23]

None of them are working 😓

[g23]

Thanks anyway

[Sameer9585]

can you send me the dataset so that I can implement and send you back

[Channa]

try dimensionality () function

[g23]

Thanks all for the help.

At the end I used the following expression for the color : 

ColorMix1( rank(total column(1))/noofrows(TOTAL) , lightgreen(),lightred())

Thanks again