topic Re: background color on set analysis in QlikView

background color on set analysis

tinkerz1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I can't set the background colour with an expression.

The expression formula switches between set analysis and normal output based on a condition.

=if(getselectedcount([Header])>1,

Sum([Alternate State 1]...<Set Analysis>),

DATA

)

The formula in the background color is

=If(Wildmatch([label_M],'*Red*'),RGB(255,0,0),
If(Wildmatch([label_M],'*Amber*'),RGB(255,191,0)))

And label_M is is a field in the pivot table.

The label_M field in both conditions returns a Red and a Amber field, why won't the colour work when the set analysis condition is true?

Thanks.

Re: background color on set analysis

sunny_talwar — Tue, 15 Mar 2016 14:11:19 GMT

Where is set analysis here? Not sure I understand the reference to set analysis

Re: background color on set analysis

tinkerz1 — Tue, 15 Mar 2016 14:39:44 GMT

This would be the full formula, the set analysis will be triggered if get selectedcount >1

=if(getselectedcount([Issue Owned by])>1,
Sum({[Stats1]<[Issue Owned by]+={"$(=Only(left([Issue Owned by],index([Issue Owned by],'-')-2)))*"}*{"*|All"},[Risk label_M]-={'*Threshold'}>} distinct [Data M])
,
[Data M]
)

Re: background color on set analysis

tinkerz1 — Tue, 15 Mar 2016 15:46:27 GMT

However the formula to set the background does not trigger if

getselected count >1

=If(Wildmatch([label_M],'*Red*'),RGB(255,0,0),
If(Wildmatch([label_M],'*Amber*'),RGB(255,191,0)))

Re: background color on set analysis

tinkerz1 — Wed, 16 Mar 2016 09:40:54 GMT

Hi,

I have written a small QVW to explain my problem.

I am using getselectedcount on a field to switch between a set analysis statement and and normal statment.

When just 1 selection from the company field is selected the background colors work but when more than one selection from the company field is selected; then no background color, the set analysis works though.

Please try.

Re: background color on set analysis

sunny_talwar — Wed, 16 Mar 2016 09:54:14 GMT

May be try this for the background color:

=if(not wildmatch(Only({<Company>} Company),'I','H'),RGB(255,0,0),

if(wildmatch(Only({<Company>} Company),'I'),RGB(255,255,0),

if(wildmatch(Only({<Company>} Company),'H'),RGB(0,255,0),

)))

Re: background color on set analysis

tamilarasu — Wed, 16 Mar 2016 09:55:50 GMT

Hi Neil,

You can try background color expression as

=if(Only({1}Company) = 'I',RGB(255,255,0),

if(Only({1}Company) ='H',RGB(0,255,0),

If(Only({1}Company)<>'I' or Only({1}Company)<>'R', RGB(255,0,0))))

Re: background color on set analysis

tinkerz1 — Wed, 16 Mar 2016 12:16:04 GMT

Thanks for the reply Tamil, however the getselected code was key to this example.

I am using getselected count as a switch. the 2 formulas after are just examples of set analysis and a normal formula.

Thanks for looking into it.

Re: background color on set analysis

sunny_talwar — Wed, 16 Mar 2016 12:22:42 GMT

Did you look at what I provided? Is this not what you were aiming for?

Re: background color on set analysis

tinkerz1 — Wed, 16 Mar 2016 12:56:33 GMT

Hi Sunny,

It did not work as the getselectedcount part was removed.

The point of the formula is to set a color when a single company is selected, but when more that one customer is selected then use another color depending on the selection.

The set analysis for more the one could be selecting all companies with an 'I' in the title, but there is only 1 in my list

its really the 'form' of the function not the actual formula

Re: background color on set analysis

sunny_talwar — Wed, 16 Mar 2016 12:58:03 GMT

We did not remove anything from the expression Neil. We only changed the expression for the background color.

UPDATE:

What it was

Background Expression:

=if(not wildmatch(Company,'I','H'),RGB(255,0,0),

if(wildmatch(Company,'I'),RGB(255,255,0),

if(wildmatch(Company,'H'),RGB(0,255,0),

)))

Actual Expression:

=if(getselectedcount(Company)>1,

Sum({<Company={I}>} distinct Sales)

Sales

)

New

Background Expression:

=if(not wildmatch(Only({<Company>} Company),'I','H'),RGB(255,0,0),

if(wildmatch(Only({<Company>} Company),'I'),RGB(255,255,0),

if(wildmatch(Only({<Company>} Company),'H'),RGB(0,255,0),

)))

Actual Expression:

=if(getselectedcount(Company)>1,

Sum({<Company={I}>} distinct Sales)

Sales

)

No change to your actual expression

Re: background color on set analysis

tinkerz1 — Wed, 16 Mar 2016 13:31:01 GMT

Sorry I must have saved the update for the color over the expression.

If more than 1 company is selected, then the set analysis part of the if statement triggers.

But this should be colored yellow as the expression points at set analysis.

Thanks.

Re: background color on set analysis

sunny_talwar — Wed, 16 Mar 2016 13:32:49 GMT

I see yellow Neil