Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
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
I have attached the sample
Count({<DATE_OF_JOINING = {"<=2015-07-31"}, QUIT_DATE={">=2015-07-31"}+QUIT_DATE -={"*"}>} EMPLY_ID)
Try
=COUNT( if (DATE_OF_JOINING <='2015-07-31 AND (QUIT_DATE >= '2015-07-31 OR isnull(QUIT_DATE) EMPLY_ID) )
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
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
Thanks Rupam. I have tried this combination earlier but "*" for some reason doesn't work in my data