Excluding a value from field by selection - Set analysis

[aunahsan]

Hello,

I'm Trying to get a calculation to work but i need to be able to select a value from a field and have it be excluded in the calculation.

let me set the stage here a little bit, i have field name [Hours] and [ProjectID].

What i'm trying to achieve is that if i make  selection on my listbox for [ProjectID] it will exclude the hours associated with that [ProjectID], or exclude multiple if i make multiple choices.

i have tried:

[ProjectID] = e([ProjectID]), in my set analysis, which has been suggested in other answers, but i am still stuck.

round about ways by using variables to manually exclude the entries using [ProjectID] -= {1,2,3} , although this works, its not what i'm looking for.

Thanks for the help.

[sunny_talwar]

What is the issue with using this

[ProjectID] = e([ProjectID])

[Sergey_Shuklin · Accepted solution]

Hello, Aun!

You can achieve this using a two variables and a function GetFieldSelections(). Please, look at the picture below. I also attached an example!

[aunahsan]

Hey Sergey, thanks very much for you comment.

the actual calculation i'm trying to do is below, with your suggestion added, i'm getting a null value as the result.

=num(SUM({$<ClientID -={1,55}>} Hours)/SUM({$<ProjectID = {$(vVar1)} >} Hours), ' #,##0.0%')

the variables used are correct, i'm not sure for the null value at the moment.

I'm trying to get some vaues to be excluded from the ProjectID field when I make a Selection on it.

Thanks.

[sunny_talwar]

Would you be able to share a sample of where you are trying this? because I don't see a reason for e() to not work...

[aunahsan]

Hey Sunny,

Yes its quite odd,  with e() it gives me a null value as-well. i am very new to Qlikview, but i am quite sure i didnt make a simple syntax error or something like that.

The calculation is done in a text field, the expression is the same as above:

num(SUM({$<ClientID -={1,55}>} Hours)/SUM({$<ProjectID = {$(vVar1)} >} Hours), ' #,##0.0%')

or for e()

num(SUM({$<ClientID -={1,55}>} Hours)/SUM({$<ProjectID = e(ProjectID)>} Hours), ' #,##0.0%')

[sunny_talwar]

Are you looking at this after making a selection in ProjectID? If not, then try this

If(GetSelectedCount(ProjectID) = 0, Num(Sum({$<ClientID -={1,55}>} Hours)/Sum(Hours), ' #,##0.0%'),

Num(Sum({$<ClientID -={1,55}>} Hours)/Sum({$<ProjectID = e(ProjectID)>} Hours), ' #,##0.0%'))