New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements Partner

Match function

Hello, I am trying to use match function but the result is not what I expected. I am using this

If(Match(name, 'X', 'Y'),
Sum([Cost])
)

When I try to select the "X" name the result sum show the sum cost of "X" and the same result if I select "Y". But if I select "X" and "Y" the result will show as "-" I also change the expression to

If(name = 'X' and name = 'Y'),
Sum([Cost])
)

If(name = 'X' or name = 'Y'),
Sum([Cost])
)

The result still the same. Could you help me?

Any advice will be greatly appreciated

1 Solution

Accepted Solutions MVP

Re: Match function

The expression using [name] without an aggregation function (known as a 'naked field name') can only return a value if there is only one possible value for [name]. Otherwise it will return null. The behaviour of a naked field is the same as one wrapped in Only(...).

If you want to sum for 'X' or 'Y', but want to respect user selections as well, then you could use something like one of these:

=Sum({<name *= {'X', 'Y'}>} Cost)    // intersection of user selections and 'X', 'Y' selection
or
=Sum({<name += {'X', 'Y'}>} Cost)    // union of user selections and 'X', 'Y' selection

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies
Valued Contributor III

Re: Match function

Use wildmatch

Match returns true false case

Channa Partner

Re: Match function

Thank you for your advice but after I try the result still the same.

If(wildmatch(name, 'X, 'Y'),
Sum([Cost])
)

If I selected only X or Y the result will show but if I selected X and Y the result will be - again

Valued Contributor III

Re: Match function

Ur missing single cote on top of X

'X'

Channa Partner

Re: Match function

Oh, thanks but after I fix the expression it still the same. If select two variables the result will not show.

Valued Contributor III

Re: Match function

May you have null values
Check data
Channa Partner

Re: Match function

Hi!

It all depends where you use it.

If you create a UI table with name as dimension, then your formula works. It also works in Load script, because the comparison happens for every row. If you try to use it without dimension, then you need to use aggregation function for name eg

If(Match(MaxString(name), 'X', 'Y'),
Sum([Cost])
)

Regards,
Sergey MVP

Re: Match function

The expression using [name] without an aggregation function (known as a 'naked field name') can only return a value if there is only one possible value for [name]. Otherwise it will return null. The behaviour of a naked field is the same as one wrapped in Only(...).

If you want to sum for 'X' or 'Y', but want to respect user selections as well, then you could use something like one of these:

=Sum({<name *= {'X', 'Y'}>} Cost)    // intersection of user selections and 'X', 'Y' selection
or
=Sum({<name += {'X', 'Y'}>} Cost)    // union of user selections and 'X', 'Y' selection

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein Partner

Re: Match function

Many thanks to all of you for good advice.