Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
EMP_RELIEVE_DATE
--------------------------
13/07/2020
24/07/2020
07/08/2020
=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > 20) ),EMP_RELIEVE_DATE))
i am getting count people who are relieving from today and past more than 30 days
I have 3 records in that count. but, I am getting "zero" count
what is the problem in the expressions.
=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > 30) ),EMP_RELIEVE_DATE))
Hi @saivina2920
Try like below
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)
try this
{<EMP_RELIEVE_DATE={'>=$(=today()-30)<=$(=today())'}>}
Hi @saivina2920
Try like below
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)
Yes..super..it's working.
i used one more option which is 10 to 30 days and <10 days. below is the statement. pls. confirm whether below statement is correct or not for 10 to 30 days.
=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() <= -10 and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() >= -20)),EMP_RELIEVE_DATE))
for less than (<5days)
=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > -5 and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() < 0) ),EMP_RELIEVE_DATE))
Hi @saivina2920
Try like below
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY')) <=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY')) "}>}DISTINCT EMP_RELIEVE_DATE)
=count({<EMP_RELIEVE_DATE = {"<=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)
you can change the number based on ur requirement
Thanks...
Is it possible to add two more fields in the below expressions.
EMP_STATUS='Current'
EMP_ORIGIN={"$(vEmpOrigin)"}
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)...??
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY')) <=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY')) "}>}DISTINCT EMP_RELIEVE_DATE)..??
=count({<EMP_RELIEVE_DATE = {"<=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)..??
Hi @saivina2920
Try like below
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"},EMP_STATUS={'Current'},EMP_ORIGIN={'$(vEmpOrigin)'}>}DISTINCT EMP_RELIEVE_DATE)
Hi Mayilvahanan,
Thanks for your reply.
When executing the real time data, again the count has coming wrong.
the attached 3 date's for your reference,
>30 Days count ==> Should come 3. but, it has come only 2 ==> wrong output
10 to 30 Days count ==> Should come 0 and there is no record past 10 to days from today. but, it has come 1 ==Wrong output
<5 Days count ==> Should come 0 and there is no record past 5 days from today. but, it has come 2 ==Wrong output
where is the problem.
below is our sql expressions.
< 10 days
-------
=count({<EMP_RELIEVE_DATE = {"<=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY'))"},EMP_STATUS={'Current'},EMP_ORIGIN={'$(vOrigin)'}>}DISTINCT EMP_ID_NUMBER)
10 to 30 days
-------------
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY')) <=$(=Date(Max(EMP_RELIEVE_DATE)-10,'DD/MM/YYYY')) "},EMP_STATUS={'Current'}, EMP_ORIGIN={'$(vOrigin)'}>}DISTINCT EMP_ID_NUMBER)
> 30 days
---------
=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"},EMP_STATUS={'Current'},EMP_ORIGIN={'$(vOrigin)'}>}DISTINCT EMP_ID_NUMBER)
Note : The important thing is, future data count from today should not come at any case.
pls. reply..this is very urgent..
attached expected output as image.
sample test date qlik file attached.