Using division in a DISTINCT expression returning an error

[#1]

I'm attempting to use the following expression:

=count(DISTINCT{$<InterimAnswerScore/InterimPointsPossible ={'>=.667'}>}INTERIMID)

I'm looking to return a count of all of the scores above 66%, and my dimension are the question types which is why it is important to differentiate based on the testID number.

However QlikView keeps stating that this is an error.

Any thoughts?

[Anonymous]

Try adding a dollar expansion :

    =count(DISTINCT{$<$(=InterimAnswerScore/InterimPointsPossible) ={'>=.667'}>}INTERIMID)

[Oleg_Troyansky]

Hi Briana,

The problem is not related to DISTINCT, but rather to the set analysis syntax. Each Set Analysis modifier should look like this:

FieldName = {values}.

You cannot formulate a modifier with an expression instead of the field (even using a dollar-sign expansion, Bill!)

Field1/Field2 = {values} - this syntax is incorrect.

You can, however, formulate an Advanced Search criteria that defines the same logical selection. I'm assuming you are interested in those values of INTERIMID where the ratio of the two listed fields is greater than 2/3. So, the valid Set Analysis condition should apply the advanced search filter to the field INTERIMID:

=count({$<INTERIMID = {"=InterimAnswerScore/InterimPointsPossible >=.667"}>} DISTINCT  INTERIMID)

If you'd like to learn more about advanced Set Analysis, check out my new book QlikView Your Business. Also, you may want to check out the agenda of the Masters Summit for QlikView, where I teach advanced Set Analysis and Advanced Aggregation.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[swuehl]

Even Oleg's revised set analysis might not work in case there are multiple combinations of InterimAnswerScore and InterimPointsPossible per INTERIMID, in a way that the search won't return an unambiguous answer.

Besides that and in general, as you probably know, set analysis is evaluated once per chart, not per dimension line, so if your expression's set analysis needs to somehow care about the current dimension line, that won't work.

Can't tell if this is the case or if we missed something else completely with the information provided.

If you have still issues with your expression, please provide more information about your data model, chart settings (dimensions) etc. Best by uploading a small sample QVW.