Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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?
Curious to know if you have already tried this
Sum({<[Sales Region] = p([Field Name])>} sales)
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.
That should not happen in both the expressions. Would you be able to share a sample qvf where it is happening?
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)?
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)