Set Analysis with Set Modifiers and Variables

[hlines_usacs]

Hello,

I am trying to use Set Modifiers within Set Analysis by using a variable. Very similar to the "Set Modifiers" portion of this thread. Set Analysis

Using the example below, I want to have 'West coast' and 'South America' replaced with a variable.

sum( <[Sales Region]={'West coast', 'South America'}>} sales)

where $(vExampleVariable) = 'West coast', 'South America' I want to use the following:

sum( {<[Sales Region]={$(vExampleVariable)}>} sales)

I have tried many different ways of writing the set analysis but can't seem to get the correct syntax.

[Anonymous · Accepted solution]

It is about Qlik Sense...
There is no variable support in Qlik Sense the same as in QlikView.  You need extensions to use variables on the front end.  I assume the problem here is the limitation of the extension.

So, instead of using a variable here, the full variable definition should be used in set:

sum( {<[Sales Region]={$(=chr(34) & concat(distinct [Field Name], chr(34)&','&chr(34)) & chr(34))}>} sales)

Tested, worked for me.

[hlines_usacs]

This did work, however I used chr(39) instead of chr(34).

BUT, I ran into another issue. When I have no current selections for [Field Name] (supposed to show data for all available values), my expression shows as NULL (doesn't show anything), but when I select all the values for that field, it returns a value. Is that normal?

[sunny_talwar]

Curious to know if you have already tried this

Sum({<[Sales Region] = p([Field Name])>} sales)

[hlines_usacs]

I got the same result as my previous comment.

When I have no current selections for [Field Name] (supposed to show data for all available values), my expression shows as NULL (doesn't show anything), but when I select all the values for that field, it returns a value.

[sunny_talwar]

That should not happen in both the expressions. Would you be able to share a sample qvf where it is happening?

[Anonymous]

The only I can add that it works for me, even if no selections made.  Both my version (with chr(34)) and the one from Sunny.

Question - why don't you use simply sum(sales)?

[hlines_usacs]

There are multiple correct answers in this thread but I could only mark one as correct.

I ended up going with the second one listed below, and I had some other code in the expression that caused the table not to show anything unless a selection was made. So my apologies! Thanks for all the help and suggestions!

sum( {<[Sales Region]={$(=chr(34) & concat(distinct [Field Name], chr(34)&','&chr(34)) & chr(34))}>} sales)

Sum({<[Sales Region] = p([Field Name])>} sales)