Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Date within an interval.

I’m working on HR reporting. I’ve built a master calendar and linked the calendar DATE to start_date. I am trying to get a proper head count which is to count the number of employees that fall within the interval from report selection. The report should count employess that falls in between the interval start_date and end end_date given the selection DATE.

My formula looks like this:

[if DATE <= start_date and DATE >= end_date then count(EmpID)]

*Note I put my formula in a text box.

I get nothing.

Any/all help/tips are appreciated.

Here is my playpen script:


Let varMinDate=num(makedate( 2001, 1, 1 ));

Let varMaxDate=num(makedate( 2013, 12, 31 ));


load date($(varMinDate)+ Iterno()-1) as Datefield


while $(varMinDate)+ Iterno()-1 <= $(varMaxDate);


load distinct

DayName(Datefield) as DATE,

Year(Datefield) as Year,

Month(Datefield) as Month,

Day(Datefield) as Day
resident Datefield;

drop table Datefield;



[EmpID, start_date, end_date
1, 3/15/2002, 9/20/2009
2, 6/1/2005,



date(start_date) as start_date,

date(end_date) as end_date,

date (start_date) as DATE

resident HR_Reporting;

8 Replies


Try count({<DATE ={">=start_date<= end_date"}>}EmpID)



Contributor II
Contributor II

Sorry Ariel... I get nothing... no joy.

I do like your syntax though..


Not applicable

I believe that the problem here is linking start_date with DATE. It could work if Calendar were unlinked with HR_reporting table but if u have many data lines it would be very slow..

Also, it could be the issue that some employees don't have end_date (you should make fake end_date like tommorow or smth)

Btw, what is the reason of concatenating HR_reporting tables?

Not applicable

try this

Count(if(DATE>=num(date(start_date) )and  DATE <= num(date(end_date)) , EmpID))

or try this

count({<DATE ={">=$(=date(start_date))<=$(=date( end_date))"}>}EmpID)

Contributor II
Contributor II

Thanks for you reply Sheshele,

I need to link the 2 tables... If I don't link DATE to start_date then how should I do it?  I need a master calendar. Hence the need to concattenate the field... which now becasue of you thinking what I have done is wrong.

If I look at my table I see that I have indeed duplicated my entry. I need to append the field DATE so that it matches the start_date.

Here is what I did:


I'm not sure how to append my fields so that I have a link.


Contributor II
Contributor II

Thanks vishwaranjan,

but this didn't work... you can run the same test using my above provided script. I think my problem is how I built the model... then your expression should work fine.


Not applicable

I dont say that u dont need a master calendar. But let's answer the question what is the DATE? U have start_date, end_date and the third DATE which is neither the start_date nor the end_date.

I have had a lot of projects where I have a lot of different dates but I need another one which is not related - we can talk about debts balance, premium earned for insurance, etc..

Saying the truth I never solved the problem properly. Currently, I know three so-so ways. The one is to have a master calendar that is not linked to the data table but when it comes to 2m unique keys multiplied by 3 years, the solution is unfortunate. The other is to use variable as a DATE u are interested. Then u get ur answer for the specific date quickly and correctly but u loose the chance to see the dynamics. The last is to do that kind of caclulations in loading script. This is the best way but still if it comes when u multiply 2m unique keys by 3 years u get 600m lines and qlikview gets just too big and too slow.

See attached qwv file. Sorry I changed dates to the format I usually use

Hope it helps.

Master II
Master II

r u tried  with floor function this code

date(floor (start_date)) as DATE

hope it helps