topic Re: Color a Dimension if it matches two parameters (if match AND) in QlikView

Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 10:59:14 GMT

I am trying to color a dimension green if the value in this dimension is accuring two times in another dimension.

Dim1: Validity, Dim2: Named Account

Validity, Named Account

ST, Loreal

MT, Loreal

MT, Cisco,

LT, Stabilo

I would like to color the Dim Named Account green if one account has the Validity ST AND MT.

I tried it with entering the Dimension and fill following formula in the "Text Color" Area of the Dimension "Named Account".

if(match(Validity,'ST') and match(Validity,'MT'),green())

Unfortunately this is not working, if just one first parameter if(match(Validity,'ST'), green()) it is working.

Does anybody know how to achieve this?

I have added a sample app, the single Account is now green due to the parameter match(Validity,'ST'), the question is how to do with an and-condition?

Re: Color a Dimension if it matches two parameters (if match AND)

prma7799 — Thu, 27 Jul 2017 11:06:57 GMT

Go to expression >> Background color >> if(WildMatch(Validity,'ST','MT') , green())

Re: Color a Dimension if it matches two parameters (if match AND)

prma7799 — Thu, 27 Jul 2017 11:07:51 GMT

Or you can write your own expression if(match(Validity,'ST') and match(Validity,'MT'),green())

Re: Color a Dimension if it matches two parameters (if match AND)

neha_shirsath — Thu, 27 Jul 2017 11:08:16 GMT

Use-

if(WildMatch(Validity,'ST','MT'),Green())

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:09:56 GMT

I would like to color the Dimension and not the Expression.

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:10:51 GMT

This statement will color all ST and all MT green.

I would just like to color those which are (ST and MT together).

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:10:56 GMT

This statement will color all ST and all MT green.

I would just like to color those which are (ST and MT together).

Re: Color a Dimension if it matches two parameters (if match AND)

prma7799 — Thu, 27 Jul 2017 11:11:46 GMT

Go to dimenssion>> Background color >> if(WildMatch(Validity,'ST','MT') , green())

Re: Color a Dimension if it matches two parameters (if match AND)

stigchel — Thu, 27 Jul 2017 11:12:37 GMT

Use concat first

=if(index(Concat(Validity),'ST') and index(Concat(Validity),'MT'),green())

Re: Color a Dimension if it matches two parameters (if match AND)

neha_shirsath — Thu, 27 Jul 2017 11:15:08 GMT

=if(Dimensionality() = 1,

colormix1(Your expression, white(), RGB(252, 85, 67)),white())

Re: Color a Dimension if it matches two parameters (if match AND)

prma7799 — Thu, 27 Jul 2017 11:16:19 GMT

PFA...

Re: Color a Dimension if it matches two parameters (if match AND)

tresB — Thu, 27 Jul 2017 11:17:52 GMT

May be like this?

Expression:

=Aggr(NODISTINCT if(index(Concat(Validity),'ST') and index(Concat(Validity),'MT'),green()),[Named Account])

Re: Color a Dimension if it matches two parameters (if match AND)

jonathandienst — Thu, 27 Jul 2017 11:19:10 GMT

No one record can have both Validity values, that is why the simple AND in your post does not work. I assume that each dimension represents multiple records, some of which may be either of the two test values. I also assume that you want to colour only those that have both test values.

Then something like this will use the intersection:

If(Count({<Validity = {'ST'}>*<Validity = {'MT'}>} [Named Account]) > 1, Green())

If that does not work, i suggest that you upload a small sample withe some representative data. The data does not have to be real data, but it must be in line with your real data model.

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:21:47 GMT

Hi Jonathan,

I think you are right, I have uploaded a really simple application. Maybe you can have a look for me?

Thanks a lot!

Chris

Re: Color a Dimension if it matches two parameters (if match AND)

tresB — Thu, 27 Jul 2017 11:24:55 GMT

Did you check my suggestion? PFA

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:26:04 GMT

I am struggling with your Set Analysis Expression, I have never seen >*< this one between the Field(s). What does this mean?

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:27:58 GMT

That looks good, I will try it in my app and brb. Thx!

Re: Color a Dimension if it matches two parameters (if match AND)

tresB — Thu, 27 Jul 2017 11:33:17 GMT

This is intersection operator. Check it here: Set analysis and set expressions ‒ QlikView

Re: Color a Dimension if it matches two parameters (if match AND)

chriys1337 — Thu, 27 Jul 2017 11:35:38 GMT

Your Code seems logical, but why did you go for NODISTINCT?

=Aggr(NODISTINCT if(index(Concat(Validity),'ST') and index(Concat(Validity),'MT'),green()),[Named Account])

Re: Color a Dimension if it matches two parameters (if match AND)

jonathandienst — Thu, 27 Jul 2017 11:39:10 GMT

Use this expression as a Text Foreground colour for Dimension Account:

=If(Count({<Account = P({<Validity = {'ST'}>} Account) * P({<Validity = {'MT'}>} Account)>} Account) > 0, Green())

Unfortunately I cannot upload the file due to the security policies here.