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

[Anonymous]

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!

[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]

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]

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.

[Anonymous]

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?

[Anonymous]

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]

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.

[Anonymous]

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‌