Function in variable / dollar sign expansion

Anonymous — Wed, 24 Jun 2015 07:04:10 GMT

I have a variable called Bottom_score which calculates the sum of bottom N scores of a question, The variable is defined like this:

Bottom_score:

sum( {<ID={"=aggr(rank(-sum($2),4,1), Survey_Name) <= $1"}>} $2)

where $1 is the N and $2 is the score of a particular question.

In the chart, I then put an expression:

$(Bottom_score(3,Q818))

So this returns me the sum of bottom 3 scores of question Q818. So far it works perfectly.

But afterwards I realised that some of the raw scores of questions contain "N/A". Ideally these results should be ignored in calculation but the above function always include those "N/A" as some of the bottom results. For some reason I can't remove / get rid of those responses in raw data, nor can I do that in loading script. Thus I would like to do this in the chart expression by trying some like this:

$(Bottom_score( 3+count( {$<Q818={'NA'}>} Q818) , Q818))

Then I found that 'count( {$<Q818={'NA'}>} Q818)' simply doesn't work. Is there any problem in the syntax? Or does it related to my use of dollar sign expansion in the expression?

Thanks.

topic Function in variable / dollar sign expansion in QlikView

Function in variable / dollar sign expansion