Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

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

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

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

By looking at the picture I notice that Your date format is not DDMMMYYYY:hh:mm:ss but MM/DD/YYYY hh.mm.ss
Try changing your expression inside the LOAD to:
Date(floor(Date#(EmpJoinDate,'MM/DD/YYYY hh.mm.ss'))) as EmpJoinDate

View solution in original post

16 Replies
Vegar
MVP
MVP

Try an expression like this one using SET analysis.

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

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
Partner - Specialist III
Partner - Specialist III

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

Screenshot_3.jpg

I have also attached qvw file.

Vegar
MVP
MVP

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. 

image.png

saivina2920
Creator
Creator
Author

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

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
Creator
Creator
Author

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

You should probably skip the brackets [] 

Try 

SQL SELECT 
EmpJoinDate
FROM 
DB.dbo.Table
;
saivina2920
Creator
Creator
Author

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";