topic Re: Qlik expressions: Multiple selections within a filter in QlikView

Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 15:50:33 GMT

Hello Qlik Community,

I am trying to write an expression where I can use the filters and data points change.

I can do if statements, such as if(country='US' and segment='Consumer', but I need help with a next step:

How would I write if there are two countries (Within the same field) Such as: If(country=US and Canada,Value

Please let me know if there is a solution to this. I appreciate it.

thank you

Ken

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 15:54:00 GMT

Use Match() function

If(Match(country, 'US', 'Canada'), Value

Re: Qlik expressions: Multiple selections within a filter

BalaBhaskar_Qlik — Thu, 26 Jul 2018 15:56:17 GMT

May be like:

If(Country = 'US' and Segment = 'Consumer', Sales,If(Match(Country,'US','Canada'),Sales,0))

Re: Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 16:00:15 GMT

Sunny - thanks for the reply but it is only picking up the first field, not all three, how should I modify it?

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 16:04:28 GMT

Can you elaborate or share a sample to show the problem?

Re: Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 16:10:56 GMT

this formula you gave only works, when US Consumer is selected, I would like it to work when US Consumer US MD&D and US Pharm are selected, thanks for your help!

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 16:15:13 GMT

All three are selected at the sample time?

Re: Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 16:16:39 GMT

Yes correct, as an example when all three (in the formula) are selected I want it to be 30MM. Thanks Sunny

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 16:18:27 GMT

May be this

If(

SubStringCount(Concat(DISTINCT '|' & [Accounting Country] & '|'), '|US Consumer|') = 1 and

SubStringCount(Concat(DISTINCT '|' & [Accounting Country] & '|'), '|US Pharm|') = 1 and

SubStringCount(Concat(DISTINCT '|' & [Accounting Country] & '|'), '|US MD&D|') = 1,

3000000000)

Re: Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 16:24:50 GMT

Didn't work either unfortunately 😕 I copied and pasted your reply in, would I have needed to modify anything? If not, is there another possibility? I appreciate the help thus far

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 16:26:47 GMT

So, let me get this straight... you used the above expression in a KPI object and you selected US Consumer, US Pharma and US MD&D in your selection box and you didn't see 3000000000?

Re: Qlik expressions: Multiple selections within a filter

Anonymous — Thu, 26 Jul 2018 16:32:04 GMT

It worked! I made a spelling error, US Pharma is supposed to be US Pharm, thanks for your help!!! you deserve a monument in here

Re: Qlik expressions: Multiple selections within a filter

sunny_talwar — Thu, 26 Jul 2018 16:43:51 GMT

That was my bad.... glad it worked