Date count not coming properly for past 30 days from today

[saivina2920]

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))

[Chanty4u]

try this

{<EMP_RELIEVE_DATE={'>=$(=today()-30)<=$(=today())'}>}

[MayilVahanan · Accepted solution]

Hi @saivina2920 

Try like below

=count({<EMP_RELIEVE_DATE = {">=$(=Date(Max(EMP_RELIEVE_DATE)-30,'DD/MM/YYYY'))"}>}DISTINCT EMP_RELIEVE_DATE)

[saivina2920]

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))

[MayilVahanan]

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

[saivina2920]

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)..??

[MayilVahanan]

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)

[saivina2920]

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.

[saivina2920]

pls. reply..this is very urgent..

[saivina2920]

attached expected output as image.

sample test date qlik file attached.