topic Creating a More efficient Set Analysis Expression in QlikView

Creating a More efficient Set Analysis Expression

Tue, 16 Mar 2010 09:43:08 GMT

Hi guys,

I'm doing some set analysis on a pie chart where we only want to show 36 out of 38 values on it. I've done this previously using set analysis which works fine. However, my method has been to do it as follows:

count( {$< FieldA = {"1", "2", "4", "5"} >} FieldB)

This therefore counts FieldB where the inputs are 1,2,4 or 5, ie, not 3.

However, I don't see this as a particularly efficient way as if I have 38 values for a field and I want to exclude 2, I don't want a set analysis expression with the other 36 contained in it. Therefore is there a way in which you can use '<>' instead of '='? This would mean I could just state the 2 values i wish to exclude. It didnt seem to work for me when i used <>.

Any suggestions? I know I could do this in the script but I want to keep all the underlying data in a raw data table.

Thanks for your help.

Creating a More efficient Set Analysis Expression

Miguel_Angel_Baeyens — Tue, 16 Mar 2010 09:50:07 GMT

Hello,

Use

Count({$< FieldA -= {3, 19} >} FieldB)

to exclude values 3 and 19, but take into account the rest of FieldA values. By the way, there is no need to quote when using numeric values.

Creating a More efficient Set Analysis Expression

skaredovs — Tue, 16 Mar 2010 10:00:21 GMT

You can do this without set analysis. Use IF.

count(IF(match(FieldA,'3','19')=0,FieldB))

Best regards,

Konstantins