Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iluilyas
Contributor III
Contributor III

Set Analysis - And and Or conditions

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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

Capture.PNG

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

View solution in original post

11 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

I have attached the sample

rupamjyotidas
Specialist
Specialist

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

vinieme12
Champion III
Champion III

Try

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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

Capture.PNG

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

Capture.PNG

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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