topic Re: Add alternate state to set analysis in QlikView

Add alternate state to set analysis

Mon, 22 Jun 2015 16:02:58 GMT

What is the correct syntax if I add an alternate state to this set analysis statement?

=count(distinct if(isnull([Gender]), [Participant_ID]))

I need to count the number of times a participant ID has an associated gender type that is null. I need to do this for state 1 and state 2, which represent different time periods.

I haven't had luck slipping in alternate state in. Your help would be appreciated.

Thank you.

Re: Add alternate state to set analysis

Mon, 22 Jun 2015 16:05:11 GMT

...one correction...I need to count the UNIQUE number of times a participant ID has an associated gender type that is null. Need for two different states.

Re: Add alternate state to set analysis

swuehl — Mon, 22 Jun 2015 16:05:21 GMT

I don't see a set analyis expression in your current statement, but you can add it like this:

=count({State1} distinct if(isnull([Gender]), [Participant_ID]))

=count({State2} distinct if(isnull([Gender]), [Participant_ID]))

Re: Add alternate state to set analysis

sunny_talwar — Mon, 22 Jun 2015 16:08:54 GMT

Try this maybe:

=Count({[State 1]} + {[State2]} DISTINCT if(isnull([Gender]), [Participant_ID]))

Re: Add alternate state to set analysis

Mon, 22 Jun 2015 16:45:56 GMT

This works! Thank you.

Two additional questions...

I have a table where I'm showing the distinct count of participant IDs for Male, Female and null. I included a multi-box filter where the end user can select if they want to see participant ID counts for just males, just females or just nulls. Null doesn't show up as an option, obviously. Any suggestions on how to get around this? I'd like the end user to only see nulls if they so choose.

Furthermore, if the end user wants to filter on only males, for example, then the participant ID counts for female and null should go to zero. This is happening for female but not null. Is there a way to get around this?

Thank you for the additional help.