topic Re: Excluding a value from field by selection - Set analysis in QlikView

Excluding a value from field by selection - Set analysis

aunahsan — Fri, 19 May 2017 16:33:46 GMT

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.

Re: Excluding a value from field by selection - Set analysis

sunny_talwar — Fri, 19 May 2017 17:01:28 GMT

What is the issue with using this

[ProjectID] = e([ProjectID])

Re: Excluding a value from field by selection - Set analysis

Sergey_Shuklin — Fri, 19 May 2017 18:17:29 GMT

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!

Re: Excluding a value from field by selection - Set analysis

aunahsan — Mon, 22 May 2017 09:24:46 GMT

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.

Re: Excluding a value from field by selection - Set analysis

sunny_talwar — Mon, 22 May 2017 10:49:59 GMT

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...

Re: Excluding a value from field by selection - Set analysis

aunahsan — Mon, 22 May 2017 11:13:12 GMT

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%')

Re: Excluding a value from field by selection - Set analysis

sunny_talwar — Mon, 22 May 2017 15:08:05 GMT

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%'))