topic Re: Dates and Calendar in App Development

Dates and Calendar

nicouek — Fri, 11 Oct 2024 13:38:50 GMT

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

Re: Dates and Calendar

Josh_Good — Fri, 11 Oct 2024 14:32:33 GMT

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)

Re: Dates and Calendar

Kushal_Chawda — Fri, 11 Oct 2024 15:57:45 GMT

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

Re: Dates and Calendar

nicouek — Sat, 12 Oct 2024 12:29:31 GMT

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

Re: Dates and Calendar

Kushal_Chawda — Fri, 11 Oct 2024 19:02:27 GMT

@nicouek try below

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

Re: Dates and Calendar

Vegar — Sat, 12 Oct 2024 06:58:15 GMT

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.

Re: Dates and Calendar

zar — Sat, 12 Oct 2024 13:10:34 GMT

Pleache check this link: https://community.qlik.com/t5/App-Development/How-could-I-have-three-differets-field-with-specific-information/m-p/2485664/highlight/true#M101303

There is similar issue / need with code example.

Re: Dates and Calendar

nicouek — Fri, 18 Oct 2024 23:12:56 GMT

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

Thank you