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

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

vjoshi2017 — Tue, 10 Oct 2017 14:38:36 GMT

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

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

sunny_talwar — Tue, 10 Oct 2017 17:13:37 GMT

May be try this

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

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

vjoshi2017 — Wed, 11 Oct 2017 09:09:13 GMT

This will work on the first level.

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

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?

sunny_talwar — Wed, 11 Oct 2017 11:12:17 GMT

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?

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

vjoshi2017 — Wed, 11 Oct 2017 11:21:01 GMT

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?

sunny_talwar — Wed, 11 Oct 2017 11:26:31 GMT

And what about the expression for the dots?

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

sunny_talwar — Wed, 11 Oct 2017 11:45:58 GMT

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

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

vjoshi2017 — Fri, 13 Oct 2017 14:15:04 GMT

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?

sunny_talwar — Fri, 13 Oct 2017 14:21:09 GMT

Try this

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

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

vjoshi2017 — Fri, 13 Oct 2017 14:33:47 GMT

It still doesn't work.

Without any filter and is correct

With selections on product groups.

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%

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

sunny_talwar — Fri, 13 Oct 2017 14:36:15 GMT

I understand what you are saying, but I don't expect to see this behavior unless you have not shared a set analysis in your expressions or something else like that... that was the reason i asked for a sample...