Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set analysis: exclude all except 1 dimension + set 1 dimension

After scouring the community, I still can't get this to work - any and all ideas would be appreciated!

Requirement:  always display a specific KPI (Alias=CTR130) for a specific year (set in a variable called vCY). The result should NOT change when selections are made, except where the user makes a selection in one dimension (Entity Name).

I can sucessfully get the following script to work:

sum({1<[Entity Name]=p([Entity Name]), Year={'2015'}, Alias={'CTR120'}>} Value)

However ... when I try to replace the year 2015 with my variable, the result (incorrectly) changes when I select different years. It presumably has to do with the $.

sum({1<[Entity Name]=p([Entity Name]), Year={$(vCY)}, Alias={'CTR120'}>} Value)

Can anyone help to show me how to use a variable inside this set analysis?

PS I was hoping to do this without having to list all of the dimensions that must be excluded.

Thank you!

8 Replies

Try this may be:

=Sum({1<[Entity Name]=$::[Entity Name], Year={'vCY'}, Alias={'CTR120'}>} Value)

I changed the first part -> [Entity Name]=$::[Entity Name] as I have learned that this is a better way to allow selections then using p()


So you are saying this fulfills your requirement

sum({1<[Entity Name]=p([Entity Name]), Year={'2015'}, Alias={'CTR120'}>} Value)

and only changing '2015' to your variable expansion $(vCY) makes it sensitive to year selections?

The variable expansion (dollar sign expansion) alone is not responsible for that. It probably depends on how you have defined your variable vCY.

Hence, what is the exact definion of vCY and what does e.g. an input box with that variable show?



It depends on how you define the variable.

Try using the "=" in the variable definition.


Use the below expression to see if it works.

sum({1<[Entity Name]=p([Entity Name]), Year={"$(=$(vCY))"}, Alias={'CTR120'}>} Value)

if still no luck, attach the sample qvw to check the issue.



Contributor III
Contributor III

Thanks for your quick reply Sunny - I've given this a try and with Year ={'vCY'] it does not return any value (ie looks like it isn't recognising it as a variable).

Any other thoughts?

Contributor III
Contributor III

Hi Swuehl

Yes, your statements are 100% correct.

As for the variable, it is defined as max(total [Year]).  It is definitely picking up the correct year (being the latest) and I have successfully used the variable elsewhere.

Any further thoughts would be most welcomed 🙂


What about this:



max(total [Year])

is sensitive to year selections, right?

I think you should define your variable as (note the leading equal sign)

=max({1} [Year])

or using any other appropriate set expression that will render your returned value insensitive to your required selections.

Check that an input box now returns a number


Then your original expressions should be now equivalent.

Contributor III
Contributor III

Swuehl:  perfect! that did the trick

FYI my final script looks like this:

Sum({1<[Entity Name]=$::[Entity Name],  Year={$(vCY)}, Alias={'CTR120'}>} Value)

this also worked

Sum({1<[Entity Name]=p([Entity Name]),  Year={$(vCY)}, Alias={'CTR120'}>} Value)

Thanks swuehl‌ and sunindia