Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
vjoshi2017
Contributor
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

10 Replies
sunny_talwar

May be try this

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

vjoshi2017
Contributor
Contributor
Author

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?

sunny_talwar

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
Contributor
Contributor
Author

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

sunny_talwar

And what about the expression for the dots?

sunny_talwar

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

vjoshi2017
Contributor
Contributor
Author

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.

sunny_talwar

Try this

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

vjoshi2017
Contributor
Contributor
Author

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%