topic Re: Color Code based on Inline Table utilizing two dimensions in that table in QlikView

Color Code based on Inline Table utilizing two dimensions in that table

Fri, 16 May 2014 05:06:06 GMT

I currently utilize color coding of cells in a straight table by using background expressions for each expression. There are two different sets of ranges per expression; I control which set by way of the variable vSLAindicator (for toggling). I'm thinking this can be simplified by creating an Inline Table with two sets of ranges, but haven't quite figured out how bust to structure this Table and what functions I could used to look up the range vales given they would be based on two dimensions in that table, i.e. some sort of set indicator and another to designate which expression these ranges apply to.

Background Expression associated to Expression 1

if(vSLAindicator = 0,
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 > 29,RGB(3,197,255),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 >= 25 and Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 <= 29,RGB(154,232,108),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 >= 20 and Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 <= 25,Yellow(),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 < 20,LightRed(),
)))),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 >= 70,RGB(154,232,108),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 >= 50 and Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 <= 70,Yellow(),
if(Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100 < 50,LightRed(),
))))

Background Expression associated to Expression 2

if(vSLAindicator = 0,
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 > 73,RGB(3,197,255),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 >= 50 and Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 <= 73,RGB(154,232,108),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 >= 40 and Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 <= 50,Yellow(),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 < 40,LightRed(),
)))),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 >= 85,RGB(154,232,108),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 >= 70 and Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 <= 85,Yellow(),
if(Sum(IF(RuntimeMins < 15,1,0))/count(Attempt)*100 < 70,LightRed(),
))))

There are 2 more like these.

Re: Color Code based on Inline Table utilizing two dimensions in that table

Anonymous — Fri, 16 May 2014 12:21:18 GMT

Help me out, I'm not exactly sure what you are trying to achieve.

Can you give a simple example?

Re: Color Code based on Inline Table utilizing two dimensions in that table

Peter_Cammaert — Fri, 16 May 2014 13:46:51 GMT

I guess it's about coloring text and background based on conditions and making this a dynamic process based on a range/color table instead of a static process that uses a (large) IF.

Looking for the same...

Re: Color Code based on Inline Table utilizing two dimensions in that table

Fri, 16 May 2014 14:21:42 GMT

LEWIS,

i feel, that we may have much more simple expression with some set analysis search expression or firstsortedvalue.

I will try to deal with it today leter or monday....

regards

Darek

Re: Color Code based on Inline Table utilizing two dimensions in that table

Fri, 16 May 2014 18:36:31 GMT

Lewis,

of course you need to adjust it into all your specific conditions of color choosing, but i hope, with this general concept it should be easy for you,

Let me know if you will have problems or questions.

regards

Darek

Re: Color Code based on Inline Table utilizing two dimensions in that table

Fri, 16 May 2014 18:48:47 GMT

Dariusz,

I need to dig further into your example to fully understand it, but on the surface it sure looks like the solution I've been looking for....very cool!

Many Thanks,

Lew

Re: Color Code based on Inline Table utilizing two dimensions in that table

Fri, 16 May 2014 19:13:16 GMT

for vSLAindicator you may use set analysis.

You may also decide to have island table with RuntaimMins cut-off and i addition calculate this part:

Sum(IF(RuntimeMins < 5,1,0))/count(Attempt)*100

in hidden column.

regards

Re: Color Code based on Inline Table utilizing two dimensions in that table

Peter_Cammaert — Fri, 16 May 2014 22:57:02 GMT

Wow, this is perfect

Thanks

Re: Re: Color Code based on Inline Table utilizing two dimensions in that table

Sat, 17 May 2014 07:32:56 GMT

Ver 1