How do we get count less than 5 days from current date

[saivina2920]

How do we get count less than 5 days from current date.

I have "column A" which contains varchar data type and i want to get count of less than 5 days from current date.

What expression we can use..?

[Vegar]

Try an expression like this one using SET analysis.

Count( {< Date={">=$(=Date(max(Date)-5))"} >}[column A])

[saivina2920]

I have tested as you mentioned the expression. 

But, it is not working.

Pls. find the sample attached file which i want to get count 1). < 10 Days and 2). 10 to 20 Days count.

[MindaugasBacius]

There is only one date that is >=Date-5:

I have also attached qvw file.

[Vegar]

You had a bit scattered data, not five days in a row. I made som adjustments to prove the consept.

LOAD * ,  
chr( mod(rowno(),4)+65) as [category a],
Date(floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')),'DD-MM-YYYY') as  NewDate
;
LOAD * Inline [
EmpJoinDate
04APR2019:09:55:01
26MAR2019:10:56:34
28MAR2019:12:37:40
03MAR2019:13:03:52
02APR2019:09:28:32
26MAR2019:09:31:16
31MAR2019:09:32:06
01APR2019:09:33:33
13MAR2019:14:03:30
05APR2019:09:40:45
26MAR2019:09:44:51
03APR2019:21:16:43
03MAR2019:08:29:20
02MAR2019:09:57:55
10MAR2019:00:31:05
26MAR2019:09:59:55
04APR2019:22:57:52
29MAR2019:07:25:20
20MAR2019:22:59:19
];

This allow me to have this default view usin the expresion in the blue text box. 

[saivina2920]

Thank you very much..
currently I'm using SQL Server as Back End and my Query is SQL SELECT Statement not LOAD from EXCEL and other source.
How do i convert my varchar value to date in my script page.
below is my script.
SQL SELECT [EmpJoinDate],
Date(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') as EmpJoinDate ==> Will this Work in SQL SELECT Statement.....???

[Vegar]

You are right, you can not use Qlik syntax inside a SQL query. Try to do your date manipulation inside a preceding load.

Table:
LOAD
Date(Floor(Date#(EmpJoinDate,'DDMMMYYYY:hh:mm:ss')),'DD-MM-YYYY') as EmpJoinDate
;
SQL SELECT
   [EmpJoinDate]
FROM 
   DB.dbo.TABLE
;

Please note that I've added a Floor() in the script, this is to store the date integer and not the timestamp value as the numeric part of EmpJoinDate. (An date in QlikView is a dual value containing  both a numeric value and a text representation ) 

[saivina2920]

Error : OLEDB read failed
SQL SELECT [EmpJoinDate]

Also LOAD EmpJoinDate will not be loading in my listbox.

but, same SQL Select EmpJoinDate  will be loading in my ListBox.

Why LOAD EmpJoinDate will not be loading in my listbox..?

[Vegar]

You should probably skip the brackets [] 

Try 

SQL SELECT 
EmpJoinDate
FROM 
DB.dbo.Table
;

[saivina2920]

No..Still blank list even if i remove brackets []

below my scripts.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=**********;Initial Catalog=*****;Data Source=TEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=********;Use Encryption for Data=False;Tag with column collation when possible=False];

LOAD *,

Date(Floor(EmpJoinDate), 'DD.MM.YYYY') AS EmpJoinDate;

SQL SELECT  EmpJoinDate
FROM
DB.dbo.EmpTable";