topic Re: exclude null value and others in set analysis in QlikView

exclude null value and others in set analysis

curiousfellow — Thu, 17 May 2018 13:44:17 GMT

I try to rank some values with set analysis. I have to exclude some dimensions and the null values.

So my expression is

rank(sum({<year ={$(=highestyear)},category={'main'},%key_question -= {'question13','question3', ''question25'}>}gemiddelde))

now have to add somewhere that a %key_question with a null value has to be excluded too, but so far i cannot find the correct syntax.

The dimension with a null value now is included in the ranking while it should not.

I tried something like :

rank(sum({<year ={$(=highestyear)},category={'main'},%key_question -= {'question13','question3', 'question25','""'}>}gemiddelde))

I am a little desperate because if have a deadline so your help will be very appreciated

Re: exclude null value and others in set analysis

sunny_talwar — Thu, 17 May 2018 13:45:48 GMT

Is it truly null or is it blank space?

Re: exclude null value and others in set analysis

curiousfellow — Thu, 17 May 2018 13:53:38 GMT

I checked the value with if(isnull(%key_question),'empty','not empty') and i get 'empty' as a result.

Re: exclude null value and others in set analysis

sunny_talwar — Thu, 17 May 2018 14:00:44 GMT

May be create a flag in the script

if(isnull(%key_question),'empty','not empty') as Flag

and then this

rank(sum({<year ={$(=highestyear)},category={'main'},%key_question -= {'question13','question3', ''question25'}, Flag = {'not empty'}>}gemiddelde))

Re: exclude null value and others in set analysis

curiousfellow — Thu, 17 May 2018 14:08:57 GMT

It is not possible to set a flag. Question is another table than the value 'gemiddelde' . So for gemiddelde there is no result.

Re: exclude null value and others in set analysis

bc-thebruuu — Thu, 17 May 2018 14:21:24 GMT

Well .. what about adding %key_question=p(%key_question)-{'question13','question3', 'question25'}

or let's suppose you have question_id dimension

you may add smthing like the following

question_id={"=$=(not(IsNull(%key_question))=0)"} ...choose question_id where given condition is met

Re: exclude null value and others in set analysis

curiousfellow — Tue, 22 May 2018 14:05:24 GMT

I still did not find the right solution.

I add a simplified qvw to show my problem. When I only enable the ranking expression then ranking is correct. When I enable other expression, the null value is ranked to.

In the example, the result of dep1 should be ranked.

Re: exclude null value and others in set analysis

sunny_talwar — Tue, 22 May 2018 14:26:14 GMT

Try this

=Rank(Sum({<department = {'dep1'}, has_score_dep_1={'yes'}>} scorelocal*-1) + Sum({1} 0))