Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
vjoshi2017
New Contributor

How to use colors based on set analysis inside if condition?

I am using colors in my scatterplot depending on the compariosion result between branch Conversion Rate vs Company Conversion Rate.

But it is not working. It works when I hard code it.

My Branch conversion rate is a field from table and company conversion rate is derived using set analysis in color section of appearance.

This doesn't work:

if(CR_Branch > Count({<[Converted]={"Converted"}>}[Converted])/Count(HSSRecordID), rgb(68,119,170),rgb(204,101,118))

This works:

if(CR_Branch > 0.3047, rgb(68,119,170),rgb(204,101,118))

The value of Count({<[Converted]={"Converted"}>}[Converted])/Count(HSSRecordID) is 0.3047.


How to make it work?


Thanks,

Vivek

Tags (2)
10 Replies

Re: How to use colors based on set analysis inside if condition?

May be try this

If(CR_Branch > Count(TOTAL {<[Converted]={"Converted"}>}[Converted])/Count(TOTAL HSSRecordID), RGB(68,119,170), RGB(204,101,118))

vjoshi2017
New Contributor

Re: How to use colors based on set analysis inside if condition?

This will work on the first level.

Capture.PNG

But if I drill my data further for example, consumables and non consumables, my Branch CR and Company CR changes..

But because of the formula I am not getting correct colors which seems to be obvious because of TOTAL

Capture2.PNG

See the colors on scatter plot doesn't makes sense now. All on the right side of line should be blue and all on the left side should be red.

Any suggestions?

Re: How to use colors based on set analysis inside if condition?

Don't think this is because of the TOTAL Keyword...What is your expression for the straight line? I am assuming that anything to the left of the line needs to be red and anything to the right needs to be blue? Right?

vjoshi2017
New Contributor

Re: How to use colors based on set analysis inside if condition?

It is a X axis reference line which is having following expression.

=Count({<[Converted]={"Converted"}>}[Converted])/Count(HSSRecordID)

It gives me correct result for the line. And yes everything to the left of line should be red and right of the line should be blue

Re: How to use colors based on set analysis inside if condition?

And what about the expression for the dots?

Re: How to use colors based on set analysis inside if condition?

Would you be able to share a sample to look at the issue?

vjoshi2017
New Contributor

Re: How to use colors based on set analysis inside if condition?

Unfortunately, I can't share the sample in public. But I can explain it here.

The dots are the number of lookups.

expression is : count(HSSRecordID).

Let me explain a bit.

Variables involved:

Branch Name

Conversion Rate(CR) = Count({<[Converted]={"Converted"}>}[Converted])/Count(HSSRecordID)

Number of lookups = count(HSSRecordID)

Expression for Color: If(CR_Branch > Count(Total {<[Converted]={"Converted"}>}[Converted])/Count(Total HSSRecordID), RGB(68,119,170), RGB(204,101,118))

Cr_Branch is calculated through ETL. It is basically the ration of number of lookups to the number of lookups converted on branch.

The objective is to show the red color where branch CR  is less then company CR and blue where it is high.

But it needs to be drillable as CR for a branch for a aparticular product could be high than CR for a company for that product group although it is less overall.

Re: How to use colors based on set analysis inside if condition?

Try this

If(Only({1} CR_Branch) > Count(Total {<[Converted]={"Converted"}>}[Converted])/Count(Total HSSRecordID), RGB(68,119,170), RGB(204,101,118))

vjoshi2017
New Contributor

Re: How to use colors based on set analysis inside if condition?

It still doesn't work.

Without any filter and is correct

Capture.PNG

With selections on product groups.

Capture2.PNG

We can see here that conversion rate for company for these two product groups is 34.36% .

Even the CR for a highlighted branch is 41% for these two product groups, it is still on the right side of line and is red. It should be blue because it is higher that 34.36%