topic Re: Using division in a DISTINCT expression returning an error in QlikView

Using division in a DISTINCT expression returning an error

Thu, 12 Nov 2015 22:32:46 GMT

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?

Re: Using division in a DISTINCT expression returning an error

Anonymous — Thu, 12 Nov 2015 23:11:54 GMT

Try adding a dollar expansion :

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

Re: Using division in a DISTINCT expression returning an error

Oleg_Troyansky — Thu, 12 Nov 2015 23:29:32 GMT

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 and Qlik Sense

Re: Using division in a DISTINCT expression returning an error

swuehl — Fri, 13 Nov 2015 00:39:47 GMT

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.