Dates and Calendar

[nicouek]

Hi everyone

I am trying to make these two dates fields return the YEAR in which an employee was active? what is the best way to handle the task?

SORTED DESC BELOW

Thank you

[Josh_Good]

I believe what you're looking for in your results is the year range, so for example "1999-2002".

The simple way would be:

• Year(StartDate) & ' - ' & Year (EndDate)  This would return a text field.

You may want to treat 1/1/2099 as "Present" so then you'd write

• Year(StartDate) & ' - ' & if (EndDate = '1/1/2099', 'Present', Year (EndDate))

If you want to sort by StartDate you could use the Dual function

• Dual(Year(StartDate) & ' - ' & if (EndDate = '1/1/2099', 'Present', Year (EndDate)), StartDate)

[Kushal_Chawda]

@nicouek  What is the expected output? How would you line to represent active employee with this data?

[nicouek]

The expected output is this, I am searching for a way to make StartDate and EndDate return a single year every time the Emp_id is present. Below you can see Emp_921 has the same StartDate and expected EndDate however each year has it's own count. This was achieved with Interval Match on MASTER Calendar which returns (e.g. 2023 = 315 Emp_id counts) 

The issue is, the client has a different solution they trust generating a staff count for them (e.g. 2023 = 296 Emp_id counts) and that's what the client wants to see in qlik. I have been able to get that number in qlik sense (296) by, filtering on StartDate column <=1/1/2023 and filter EndDate column >=1/1/2023. Now I want to find out if I can categorize Startdate and EndDate to apply this filtration in the engine when I select the Year I want to analyze. + I have to find a way to categorize them into their years first.

Thank you

I hope I made sense...

[Kushal_Chawda]

@nicouek  try below

Load *,
     year(YearStart(StartDate,IterNo()-1)) as Year
FROM Table
while YearStart(StartDate,IterNo()-1) <= YearStart(EndDate);

[Vegar]

If you have a master calendar containing both a date field and a year field then you could interval match you employees to that calendar. 

1. Load your master calendar, containing `date` and `year`.

2. Load your employee table containing `StartDate` and `EndDate`.

3. Create an intervalMatch table using this script.

IntervalMatchTable:

IntervalMatch(date)

LOAD StartDate, EndDate RESIDENT EmployeeTable;

This will give you a synthetic key, but that is OK. (ImtervalMatch is in general the only scenario where I keep synthetic keys in my data models) . If you don't like that, the do a community search on how to remove synthetic keys.

[zar]

Pleache check this link: https://community.qlik.com/t5/App-Development/How-could-I-have-three-differets-field-with-specific-i...

There is similar issue / need with code example.

[nicouek]

Thank you for the input however, I already you this method, but the number being returned is not what the client. 

Thank you