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