Date count not coming properly for past 10 days and 10 to 20 days from today

[saivina2920]

EMP_RELIEVE_DATE
--------------------------
13/07/2020
24/07/2020
07/08/2020

08/12/2020

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 (<10 days)

=count(DISTINCT if((Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() > -10 and Date(EMP_RELIEVE_DATE,'DD/MM/YYYY') - Today() < 0) ),EMP_RELIEVE_DATE))

[Kushal_Chawda]

@saivina2920  without looking into actual data it will be difficult say what's wrong. try to set format of variable in main tab according to format of source data. Let's say your EMP_RELIEVE_DATE format coming from source is "DD-MM-YYYY" or "DD-MM-YYYY hh:mm:ss" then set the below variable 

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss';

Change the format according to your source data format and load EMP_RELIEVE_DATE without any formatting. Then expression suggested in previous reply should work

[saivina2920]

My source data is coming from oracle and format of source field data type is "DATE".

All the stored data is coming in oracle "01-MAR-20" (Year will be stored in oracle only "20" instead of 2020)

i used in qlivkiew date format settins as below.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-IN';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

So, the current set up is below.

Oracle Data field is "01-MAR-20"  (Year will be stored in oracle only "20" instead of 2020)

my qlik query page as below

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

LOAD EMP_RELIEVE_DATE;

select nvl(EMP_RELIEVE_DATE,'') AS EMP_RELIEVE_DATE;

Attached file test oracle data with sql expression for your reference.

if the attached is the my original copy file. if this solved, then my problem is over...

[saivina2920]

Thanks Kush for your extradentary help.

I have modified as per source date. it's coming perfect.

How to avoid blank or "01-JAN-01" fields.

I used variables to avoid more confusions. below is the expression for ">30 days". It's coming correct.

But, It's taking "01-JAN-01" also. I want to avoid this also blank if any.

=Count({<EMPDATE={"<=$(=vCalDate30)"}>}EMPDATE) ==> Correct

need to add more conditions to avoid junk fields like, if any "01-JAN-01" or blank..

?????

[Kushal_Chawda]

@saivina2920  what is the expression is vCalDate30?.  Why do you need to avoid junk as your set condition is not today()?

[saivina2920]

 vCalDate30 = =Date(vToday-30,'DD-MMM-YY') 

From today, let us consider for 30days. 14-NOV-20..

i want the record after past days of 14-NOV-20. 

So, i need all the count of past days records after 30 days from today.

Find the attachment and check >30 days including the count of "01-JAN-01".

So i want to avoid the junk and blank.

Find the attached for your reference.

[Kushal_Chawda]

@saivina2920  see the attached

[saivina2920]

> 30 days count is wrong.

It should come only before date of 14-NOV-20.  (like past days of 14-NOV-20,13-NOV-20,12-NOV-20,etc)

But, your expression for 30 days count is showing after date of 14-NOV-20. (NOT like next days of 14-NOV-20,15-NOV-20,16-NOV-20,etc)

Today-30 days ==> Don't consider..Consider only after that. 14-NOV-20,13-NOV-20,12-NOV-20,etc

[Kushal_Chawda · Accepted solution]

@saivina2920  see the attached

[saivina2920]

You rock it..It's working...

What is the makedate and what it does..?

>=$(=makedate(2020,1,1)) ===> ????