topic Re: How to exclude specific value for data in cross table with 3 group of set analysis ? in QlikView

How to exclude specific value for data in cross table with 3 group of set analysis ?

Thu, 05 Sep 2013 10:29:32 GMT

Hi All,

I am trying to exclude a specific value from my data in a crosstable. However, when I tried the expression below, the straight table show -. Would appreciate if someone could point to me where I have gone wrong for the expression below ?

SUM({1<EOR_Subject_Name = {[Subject_A]}> * 1<EOR_Program = {[Program_A]}>}

* 1<Data -= {[N/A]}>}Data)

/Count({1<EOR_Subject_Name = {[Subject_A]}> * 1<EOR_Program = {[Program_A}>} * 1<Data -= {[N/A]} EOR_Subject_Name)

Background: The Data is data which are feedback score extracted from a survey. In the Data itself, there is a value called "N/A" and this affect the average score that I am trying to calculate. I need to omit the count whenever Qlikview detect "N/A" value.

Thanks in advance to any one who could shed some light on this.

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Nicole-Smith — Thu, 05 Sep 2013 17:43:43 GMT

N/A isn't actually a field, it is a string within your Data field, so it shouldn't be in brackets, it should be in single quotes:

i.e. <Data-={'N/A'}>

I'm guessing this may also be wrong with the other fields you are using in set analysis (i.e. 'Subject_A' and 'Program A').

When you're working with set analysis, I suggest you start with just one part in there, then add another one in one at a time, so you can tell which parts are/are not working.

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Anonymous — Thu, 05 Sep 2013 19:39:58 GMT

In addition to Nicole Kowalsky's...

The are some syntax problems as well. Try this:

SUM({1<EOR_Subject_Name = {'Subject_A'}> * 1<EOR_Program = {'Program_A'}> * 1<Data -= {'N/A'}>} Data)
/Count({1<EOR_Subject_Name = {'Subject_A'}> * 1<EOR_Program = {'Program_A'}> * 1<Data -= {'N/A'}>} EOR_Subject_Name)

Besides, there is no need for all these '*' here. The same logic can look simpler:

SUM({1<EOR_Subject_Name = {'Subject_A'}, EOR_Program = {'Program_A'}, Data -= {'N/A'}>} Data)
/Count({1<EOR_Subject_Name = {'Subject_A'}, EOR_Program = {'Program_A'}, Data -= {'N/A'}>} EOR_Subject_Name)

Regards,
Michael

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Fri, 06 Sep 2013 01:57:40 GMT

Thanks Nicole for pointing this out.

My initial thoughts are both [] and '' are used to define as string. Hope to get better understanding here, when will be the time to use [] and ''

I will change my [] to '' for my expression.

Noted on your suggestion, will try it out again on this. Thanks!

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Fri, 06 Sep 2013 02:01:11 GMT

Hi Michael,

The * was used as I got it from the forum here when trying to find filter condition that apply as intersection. E.g. I want to get the value only from Subject_A and Program_A

Will be trying the comma instead as this may solve my problem. Thanks and wlll update again.

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Fri, 06 Sep 2013 02:13:29 GMT

Hi All,

the comma expression works, I get the correct result that I wanted. Thanks to Nicole and Michael!

I tried 2 expressions.

Expression 1 ( with ''):

SUM({1<EOR_Subject_Name = {'Subject_A'}, EOR_Program = {'Program_A'}, Data -= {'N/A'}>} Data)

/Count({1<EOR_Subject_Name = {'Subject_A'}, EOR_Program = {'Program_A'}, Data -= {'N/A'}>} EOR_Subject_Name)

Expression 2 ( with []):

SUM({1<EOR_Subject_Name = {[Subject_A]}, EOR_Program = {[Program_A]}, Data -= {[N/A]}>} Data)

/Count({1<EOR_Subject_Name = {[Subject_A]}, EOR_Program = {[Program_A]}, Data -= {[N/A]}>} EOR_Subject_Name)

However, both expression gave the same result. Perhaps, it would be great if any one can share with us the difference between [] and single quote usage.

Thanks again for all your help.

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Anonymous — Fri, 06 Sep 2013 14:01:43 GMT

The brackets and double quotes are used for the field names (not needed if there are no spaces in field name, but no harm either), the single quotes are used for the string values. So, in this case I expected single quotes to work, and [] not to work. That they work here is news to me... But I'm not going to use it in situation like this anyway.

Apparently the real problem was the misplaced '>' and '}' which I fixed.

Regards,

Michael

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Mon, 09 Sep 2013 03:16:25 GMT

Thanks Michael for the information.

Re: How to exclude specific value for data in cross table with 3 group of set analysis ?

Nicole-Smith — Mon, 09 Sep 2013 15:48:40 GMT

Please mark correct and helpful answers so that other people having similar issues can find solutions to their problems too