topic Set Analysis Issue in QlikView

Set Analysis Issue

lucas4bi — Tue, 05 Jul 2011 15:34:17 GMT

Hello everyone,

i have an issue that i dont really know how to solve with set analysis:

i need to calculate an expression like sum(value) not affected by any selection, (so i would normally use a {1} in the expression) except for Field1 and Field2,which selections should affect the expression. how could i do this??

Thank you in advance

Set Analysis Issue

erichshiino — Wed, 06 Jul 2011 13:08:32 GMT

Hi,

You will need to include all your fields (expect the ones you want to consider) in set analysis, like this:

sum( {$<Field3 = , Field4 = , Field5 = >}value) // This will ignore selections in fields Field3, Field4 and Field5

Hope this helps,

Erich

Set Analysis Issue

lucas4bi — Wed, 06 Jul 2011 13:28:16 GMT

Thank you Erich, this is how i would normally fix the problem, but since i would have to add hundres of fields like this, i was wondering if there was a faster way to get the same result, something like {1-<Field1, Field2>} even if this expression doesnt work well...

Set Analysis Issue

Wed, 06 Jul 2011 13:41:33 GMT

In that case, I would recommend that you develop a script to create the expression. Then, paste the expression into QlikView. Something quick in Python, AutoHotkey, Ruby, or your favorite programming language should do the trick.

Set Analysis Issue

erichshiino — Wed, 06 Jul 2011 18:58:44 GMT

You can use an expression to write this sequence of fields for you.

You can create a variable vText with the following expression:

=Concat( {<$Field-={'Field1','Field2'}>} '['& $Field & ']= ',',' ) //dont forget the =

This expression will not include Field1 and Field2 in the string.

Then, on set analysis, it will be:

sum {<$(vText)>} value)

Hope this helps,

Erich

Re: Set Analysis Issue

IAMDV — Wed, 06 Jul 2011 20:35:19 GMT

Hi Mate,

Erich solution works and I had just improved it to make it generic.

=COUNT({$<[$(=Concat({1<$Field-={'Gender'}>}distinct $Field,'],[')&']=')>} Resp_ID)

In the above example except {'Gender'} all the other fields won't allow selections. You can seperate with comma to include more field names.

I hope this helps!

Cheers - DV