exclude null value and others in set analysis

[curiousfellow]

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

[sunny_talwar]

Is it truly null or is it blank space?

[curiousfellow]

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

[sunny_talwar]

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

[curiousfellow]

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

[bc-thebruuu]

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

[curiousfellow]

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.

[sunny_talwar]

Try this

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