14 Replies Latest reply: Jul 18, 2017 7:26 AM by Sunny Talwar

# Regarding color by expression by comparing rows to overall

Hi Community,

I am a novice stuck in an issue for past week.

I have a pivot table where I am created the total by using qlik feature of showing overall by going to general tab.

But I have to compare the values with this  overall

eg

if (overall>count({\$<[City]={'New York'}>} City)/Count(No of Employees),green(),white)

Especially requesting celambarasan sir as he has helped in a post similar to it but I am not able to figure it out.

Regards,
Vinayak

• ###### Re: Regarding color by expression by comparing rows to overall

Hi Vinayak

What is the Overall here? What is the expectation for the color concept?

What are dimensions used in the pivot?

Regards,

Celambarasan

• ###### Re: Regarding color by expression by comparing rows to overall

HI Celambarasan

Thanks a lot sir for helping me
I have attached my app and data with this reply.

by over all I mean the

Show totals slider

my expectation from color concept is to color values which are greater than the (totals*1.1) red and to color the values which are less than (totals*0.9) green and values which are between (totals*1.1 )and ( totals*0.9) as yellow.

• ###### Re: Regarding color by expression by comparing rows to overall

Based on your sample, would you be able to export your Qlik Sense chart and point out what cells need to be covered and with which color?

• ###### Re: Regarding color by expression by comparing rows to overall

Hi Sunny

The value marked in red which is totals of the values for may 15 should be compared with each cell of that particular column. for example
for the month of may '15  total value of percentage bad is 41.6% taking any example city like bangalore with value of 38.8%

similarly for the month of Nov '15percentage bad is 29% barcalona39.8%.

now I have to create one color expression where

if value present in bangalore/barcalona is greater than 110% of value present in totals then it should be red and if the value present in bangalore/barcalona is lesser than 90% of value present in totals then it should be green.

like;

for Bangalore

May 15

38.8%(bangalore in may15) compared with (1.1*41.6=45.76)

as it is less than 110%of totals it will go to another if condition which is
if is lesser than 90% of value present in totals then it should be green

38,8%(bangalore in May15) compared with(0.9*41.6=37.44) hence it will be neither green nor red.

similarly

Nov 15

26,5%(bangalore in nov 15) compared with (1.1*29=31.9) hence again it will go to other if condition

26.5(bangalore in May15) compared with(0.9*41.6=37.44) hence it will be green.

.

for Barcalona

May 15

60%(Barcalona in nov 15) compared with (1.1*29=31.9)(barcalonamay15 is greater than 110%of totals it will be red

Nov 16

39.8%(Barcalona in nov 15) compared with (1.1*29=31.9)(barcalonamay15 is greater than 110%of totals it will be red

Sample will look like this for Bangalore and Barcalona.

jmcsinanozdemiramber2000 if you could also help me on this.

Thanks alot

Regards,
Vinayak

• ###### Re: Regarding color by expression by comparing rows to overall

Try this

• ###### Re: Regarding color by expression by comparing rows to overall

HI Sunny,

Thanks a ton for your help sir.it worked great.

if you could explain me the logic behind it as I am very new to this tool.

Also I have two questions which I am not able to find answers to.

1.I am unable to convert null values to zeros.

2.I have to create a filter which can show data top 10 top 5 top 20 on the basis of count(id) measure.

like a drop downbox or  buttons

again thank you sir.

Regards,

Vinayak

• ###### Re: Regarding color by expression by comparing rows to overall

I can check the 1st part of the question, but the 2nd part is unrelated question and I would suggest you to create a new discussion thread for it.

• ###### Re: Regarding color by expression by comparing rows to overall

Tried looking at this, but not really sure I know why this isn't working. May be a Qlik Sense expert might be able to help better here

• ###### Re: Regarding color by expression by comparing rows to overall

HI Sunny,

Sir thanks alot it worked like a charm I had to add one more functionality which I tried adding myself but it is showing an error of if takes 2-3 parameters (feeling stupid to ask these many questions)but I am finding no end to it.
Attaching my code here:

If(

>

(Count(TOTAL <Period>{\$<[Category]={'Bad'}>} Category)/Count(TOTAL <Period> ID))*1.1, Red(),

If(

<

(Count(TOTAL <Period>{\$<[Category]={'Bad'}>} Category)/Count(TOTAL <Period> ID))*0.9, Green(),

if(

<

AND

if(

>

))))

• ###### Re: Regarding color by expression by comparing rows to overall

It seems that the third if statement is considering everything else, if that is true, why not just use this

• ###### Re: Regarding color by expression by comparing rows to overall

HI Sunny,

Sir the last one doesnt  include everything else
it is for the values which lie in between 0.9 and 1.1
0.9<value<1.1

there are values which dont fall under it

Regards,

Vinayak

• ###### Re: Regarding color by expression by comparing rows to overall

That's not what I mean...

Part 1 -> Value >1.1* Total

Part 2 -> Value < 0.9 * Total

Part 3 -> Value in between 1.1 * Total and 0.9 Total

Doesn't all the values fall under these three? There could be another part possible?

• ###### Re: Regarding color by expression by comparing rows to overall

Hi Sunny,
Sorry I couldnt explain that well

it is also coloring totals row yellow as well which should remain colorless

Regards,

Vinayak

• ###### Re: Regarding color by expression by comparing rows to overall

Try this

If(Dimensionality() > 0,