topic Re: Coloring table cell with IF statement in QlikView

Coloring table cell with IF statement

Wed, 28 Oct 2015 19:13:50 GMT

I have a similar table in Qlik Sense:

I have a column for:

Physician

AVG RVU (productivity number)

Varience

What I would like to do is color the cell red or green depending on the variance being a positive or negative change. Coloring the entire cell or just the arrow will work.

The expression for the varience is:

avg(total total_rvu)-avg(total_rvu)

Thank you in advance.

Re: Coloring table cell with IF statement

Wed, 28 Oct 2015 19:32:41 GMT

Correct me if i'm wrong. You are trying to do this in Qlikview? if so.

If its a straight table u use visual que's & use the color expression there, also u can try by giving color expression in the dimension or KPI pivot's. like in the image below.

Thanks

Brad.

Re: Coloring table cell with IF statement

Wed, 28 Oct 2015 19:36:31 GMT

I am using Qlik Sense

Re: Coloring table cell with IF statement

Anonymous — Wed, 28 Oct 2015 19:43:06 GMT

Are you trying to do this in Qlik Sense?

If so,you can use a straight table -> data -> background color, you can put the expression

if(varience>0, green(),red())

Best Regards

Re: Coloring table cell with IF statement

Wed, 28 Oct 2015 19:50:40 GMT

I tried this:

if(avg(total total_rvu)-avg(total_rvu))>0,green(),red())

But I didn't get the desired results

Re: Coloring table cell with IF statement

Thu, 29 Oct 2015 11:07:56 GMT

Hi Nathan,

Your formula is nearly there, but think it is a bracket issue, please try;

if((avg(total total_rvu)-avg(total_rvu))>0,Green(),Red())

Regards,

Jason.

Re: Coloring table cell with IF statement

chancekbarkley — Thu, 29 Oct 2015 12:18:17 GMT

Nathan,

It looks like you may have a misplaced parenthesis that is terminating the if statement prematurely. Try this:

if(avg(total total_rvu)-avg(total_rvu)>0,green(),red())

or this:

if((avg(total total_rvu)-avg(total_rvu))>0,green(),red())

-cb

Re: Coloring table cell with IF statement

Thu, 29 Oct 2015 14:24:09 GMT

I have it working, sort of. I used if(((avg(total total_rvu)-avg(total_rvu)))>0,green(),red()) in the background color expression and in the normal expression.

Is there a way to display ▼/ ▲ instead of the RGB(128,0,0) ?

Re: Coloring table cell with IF statement

chancekbarkley — Thu, 29 Oct 2015 15:37:49 GMT

Have you tried using the same formula and replacing Green with '▲' and red with '▼' for the expression?

Re: Coloring table cell with IF statement

Thu, 29 Oct 2015 16:05:32 GMT

I have, that did not work, it's saying that ▼ is not a valid function

Re: Coloring table cell with IF statement

chancekbarkley — Thu, 29 Oct 2015 16:31:23 GMT

Nathan, i just tried it and this worked for me.

Use this in your Expression:

if((avg(total total_rvu)-avg(total_rvu))>0,'▲','▼')

Use this for your background color:

if((avg(total total_rvu)-avg(total_rvu))>0,Green(),Red())

Re: Coloring table cell with IF statement

Thu, 29 Oct 2015 16:37:59 GMT

Hi Nathan,

Have you tried for the main expression

if((avg(total total_rvu)-avg(total_rvu))>0,'qmem://<bundled>/BuiltIn/arrow_n.png','qmem://<bundled>/BuiltIn/arrow_s.png')

But you must select (from Expressions tab) Display options Image (see picture)

And keep

if((avg(total total_rvu)-avg(total_rvu))>0,Green(),Red())

As the colour format?

Fingers crossed, we'll all get there!

Regards,

Jason.

Re: Coloring table cell with IF statement

Thu, 29 Oct 2015 17:17:05 GMT

Thanks, this did work!