topic Re: Color Dimension result in QlikView

Color Dimension result

dmxmikey — Mon, 10 Nov 2014 16:58:13 GMT

Have the attached script that I would like to highlight results depended on result

Attached is my script

Re: Color Dimension result

anbu1984 — Mon, 10 Nov 2014 17:10:02 GMT

Attachment is missing

Re: Color Dimension result

dmxmikey — Mon, 10 Nov 2014 17:11:59 GMT

Here is the attachment sorry

Delivering the best in Foodservice...

Re: Color Dimension result

dmxmikey — Mon, 10 Nov 2014 17:26:41 GMT

Sorry am a bit new to scripting, basically what I need to do is for the results on the table its either a Y or N, so what i would like is to get the Y in a green background and N in red background.

Below is the script im using.

=aggr( Concat( DISTINCT

if([ALL.alg-code]='GLUT',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-')

,[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)

Re: Color Dimension result

stigchel — Mon, 10 Nov 2014 17:42:03 GMT

You already have a another thread open with almost the same question (script to return image if its a certain value) Instead of opening new threads with the same try to explain better what your problem is, with examples of what you have and what you need.

If this is your dimension(?) in e.g. a straight table(?) and you want different colors for the Y and N, click on the plus sign of the dimension and in Background color use this same expression, but instead of the Y and N use an rgb code.

=aggr( Concat( DISTINCT

if([ALL.alg-code]='GLUT',if([ALL.alg-stat]='N',rgb(255,0,0),if([ALL.alg-stat]='Y',rgb(0,255,0),)),null()),'-')

,[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)

Re: Color Dimension result

dmxmikey — Mon, 10 Nov 2014 17:53:23 GMT

What I would like is when I populate my report the results chow as below screenshot where Y is filled with green and N with red, have tried your example buy there is no is no change in color

Delivering the best in Foodservice...

Re: Color Dimension result

stigchel — Tue, 11 Nov 2014 10:02:53 GMT

There is very little info for me to go on and you seem to have difficulties in explaining where the problem is.

In general you need an expression which results in a green or red color in the same way you determine whether it is a Y or N. This expression needs to be either in the background color of the dimension or the expression.

If you need more help then please post a sample of your qvw file, if you're worried about data sensitivity then please read this:

Preparing examples for Upload - Reduction and Data Scrambling

Re: Color Dimension result

dmxmikey — Tue, 11 Nov 2014 10:21:06 GMT

Thanks for that info, I have attached my qvw. And you will note on my dimension background I have put the script to color the Y in Green and N in red but the actual output is not colored.

Thanks Mike

Delivering the best in Foodservice...

Re: Color Dimension result

jpenuliar — Tue, 11 Nov 2014 10:49:30 GMT

Hi Mike,

try the below code for peanuts at background color section of your calculated dimension

=if(aggr( Concat( DISTINCT

if([ALL.alg-code]='PNUT',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-')

,[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)='N',RGB(255,0,0),RGB(0,255,0))

regards,

Re: Color Dimension result

stigchel — Tue, 11 Nov 2014 11:25:29 GMT

This should work for Gluten

=if(

aggr( Concat( DISTINCT

if([ALL.alg-code]='GLUT',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-')

,[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)

='N',rgb(255,0,0),rgb(0,255,0)

)

Re: Color Dimension result

dmxmikey — Tue, 11 Nov 2014 11:30:06 GMT

Hi Jonathan,

Thanks for that script, seems to be working and have amended the other data, is it possible to change the script so where there is no data that field is left uncoloured?

Thanks for your help

Delivering the best in Foodservice...

Re: Color Dimension result

jpenuliar — Tue, 11 Nov 2014 11:45:32 GMT

possibly this:

=if(aggr( Concat( DISTINCT if([ALL.alg-code]='PNUT',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-'),[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)='N',RGB(255,0,0),

if(aggr( Concat( DISTINCT if([ALL.alg-code]='PNUT',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-'),[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)='Y',RGB(255,0,0),''))

basically, added another IF (expression is 'Y') then change bg color to Green, then at the end you have this part ,'')) which is any other result aside from 'N' and 'Y'.

Re: Color Dimension result

jpenuliar — Tue, 11 Nov 2014 11:48:47 GMT

I dont see any null values on your attached file, i haven't really dig in to your calculated dimension expression, but is there any chance you can have a null result?

Looking at your ALL table, alg-stat is 100% dense.

Re: Color Dimension result

dmxmikey — Tue, 11 Nov 2014 12:12:06 GMT

This works perfect.

Thanks for your help

Delivering the best in Foodservice...