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

[chriys1337]

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?

[prma7799]

PFA...

[tresesco · Accepted solution]

May be like this?

Expression:

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

[jonathandienst]

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.

[chriys1337]

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

[tresesco]

Did you check my suggestion? PFA

[chriys1337]

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

[chriys1337]

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

[tresesco]

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

[chriys1337]

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])

[jonathandienst]

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.