Set Analysis - And and Or conditions

[iluilyas]

Dear Members

I'm very new to Qliksense and using Qliksense 3.0 desktop version to incorporate below SQL condition.

"SELECT COUNT(1) FROM EMP_MASTR WHERE DATE_OF_JOING <= '31-JUL-2015'

AND (QUIT_DATE IS NULL OR QUIT_DATE >= '31-JUL-2015')"

Below is the snippet of my set analysis condition. But it's not returning the right values. Its returning  as "-"

Importantly, I have a feeling that I'm not getting the QUIT_DATE records that are NULL.

Your help is very much appreciated.

Regards

Ilyas

[sunny_talwar]

May be like this:

Count({<DATE_OF_JOINING = {"<=2015-07-31"}, EMPLY_ID = {"=QUIT_DATE >= MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0"}>} EMPLY_ID)

[iluilyas]

Hi Sunny

Thank you for your response.

Unfortunately, this is not working and the count I get is "11085". This is the count I get when I query as below:

Actually the count is matching when I exclude joining date in my Sql query.

I tried to put only the quit date condition as below and the count matches.

Count({$<EMPLY_ID = {"=Len(Trim(QUIT_DATE)) = 0"}>} EMPLY_ID)

Even I tried as below but apparently, the condition is excluding join date.

Count({$<EMPLY_ID = {"=DATE_OF_JOINING=MakeDate(2015, 7, 31)"}, EMPLY_ID = {"=QUIT_DATE>=MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0"}>} EMPLY_ID)

Below is your condition:

Count({$<DATE_OF_JOINING = {"<=2015-07-31"}, EMPLY_ID = {"=QUIT_DATE>=MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0"}>} EMPLY_ID)

Regards

Ilyas

[sunny_talwar]

Would you be able to share a sample of your application? It is difficult to troubleshoot without looking at a sample

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[iluilyas]

I have attached the sample

[rupamjyotidas]

Count({<DATE_OF_JOINING = {"<=2015-07-31"}, QUIT_DATE={">=2015-07-31"}+QUIT_DATE -={"*"}>} EMPLY_ID)

[vinieme12]

Try

=COUNT( if (DATE_OF_JOINING <='2015-07-31 AND (QUIT_DATE >= '2015-07-31 OR isnull(QUIT_DATE) EMPLY_ID) )

[sunny_talwar · Accepted solution]

Based on the image you have for your query, it seems you have commented out DATE_OF_JOING part of the query to get 11,085. I have gotten very close to get 11,084

Expression used:

Count({<EMPID = {"=QUIT_DATE >= MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0"}>} EMPID)

Now, when I filter the data in Excel also, I get the same Exact result

Now if you want to add the Date of Joining, I get a result of 9,309. I am attaching the QVF file for you to look at.

HTH

Best,

Sunny

[iluilyas]

Thanks Sunny. This worked perfectly.

However, I have difficulty understanding your query. Will appreciate if you can brief me on the concept you have used here.

Regards

Ilyas

[iluilyas]

Thanks Rupam. I have tried this combination earlier but "*" for some reason doesn't work in my data