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.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sydtriman
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
sunny_talwar

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()

swuehl
MVP
MVP

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?

hemhund2016
Creator
Creator

Hi,

It depends on how you define the variable.

Try using the "=" in the variable definition.

Or

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.

Cheers,

HK.

sydtriman
Contributor III
Contributor III
Author

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?

sydtriman
Contributor III
Contributor III
Author

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 🙂

sunny_talwar

What about this:

Year={'$(=vCY)'}

swuehl
MVP
MVP

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

2015

Then your original expressions should be now equivalent.

sydtriman
Contributor III
Contributor III
Author

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