topic How could I have three differets field with specific information from Hire and leave days. in App Development

How could I have three differets field with specific information from Hire and leave days.

migueldfr_23 — Mon, 07 Oct 2024 15:14:13 GMT

Hello folks,

Hope this message fins you well, and could help me out with this matter.

I stuck with app about information of employees from a any company.

I would like to have all the info about when has been hired and when has been left.

1. if start_date = cannonical_date = hired_date, if termination_date = cannocinal_date = left_day, '-'

2. if Month(start_date) = Month(cannonical_date) then hired_month, if Month( termination_date) = month(cannocinal_date) then hired_month, '-'

3. if Year(start_date) = Year(cannonical_date) then hired_year, if Year( termination_date) = Year (cannocinal_date) then hired_year, '-'

Actually the last part of the leftmonth is wrong because it should be '-'

this is the script that I have created to retrieve the logic listed.

// Crear el mapeo para las fechas de entrada (start_date) person_id2start_date: MAPPING LOAD person_id, start_date RESIDENT hcm_workers; // Crear el mapeo para las fechas de salida (termination_date) person_id2termination_date: MAPPING LOAD person_id, If(IsNull(termination_date), Today(), Date(termination_date)) as termination_date RESIDENT hcm_workers; DateBridge: LOAD person_id & '-' & full_name as Key_person_id_full_name, Date(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) as CanonicalDate, Year(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) as CanonicalYear, Month(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) as CanonicalMonth, // Día (hiredday o leftday) If( ApplyMap('person_id2start_date', person_id) + IterNo() - 1 = ApplyMap('person_id2start_date', person_id), 'hiredday', If( ApplyMap('person_id2termination_date', person_id) + IterNo() - 1 = ApplyMap('person_id2termination_date', person_id), 'leftday', '-' ) ) as datetypedia, // Mes (hiredmonth o leftmonth) If( Month(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) = Month(ApplyMap('person_id2start_date', person_id)) AND Year(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) = Year((ApplyMap('person_id2start_date', person_id))), 'hiredmonth', If( Month(ApplyMap('person_id2termination_date', person_id)) = Month(ApplyMap('person_id2termination_date', person_id) + IterNo() - 1) AND Year(ApplyMap('person_id2termination_date', person_id)) = Year(ApplyMap('person_id2termination_date', person_id) + IterNo() - 1), 'leftmonth', '-' ) ) as datetypemonth, // Año (hiredyear o leftyear) If( Year(ApplyMap('person_id2start_date', person_id) + IterNo() - 1) = Year(ApplyMap('person_id2start_date', person_id)), 'hiredyear', If( Year(ApplyMap('person_id2termination_date', person_id)) = Year(ApplyMap('person_id2termination_date', person_id) + IterNo() - 1), 'leftyear', '-' ) ) as datetypeyear RESIDENT hcm_workers // El ciclo debe continuar hasta el último día trabajado WHILE ApplyMap('person_id2start_date', person_id) + IterNo() - 1 <= Today() AND ApplyMap('person_id2start_date', person_id) + IterNo() - 1 <= ApplyMap('person_id2termination_date', person_id, Today());

I would like to achieve the porpuse, but leftmont, leftyear and leftday, does not show up on my table. so I am not doing in the right way.

I would appreciate any help

Thank you

Re: How could I have three differets field with specific information from Hire and leave days.

zar — Tue, 08 Oct 2024 18:29:36 GMT

Hi,

Please show the example of the raw data and the example (can be just some screenshot of excel) what do you actually want.

If you have employee start date and end date, just derivate Month and Year from there. Use Intervalmatch to "expand" the range and have your date field key for Canonical Calendar.

Intervalmatch related course: https://learning.qlik.com/enrol/index.php?id=1847

About creating the calendar: https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Without more info its hard to help you.

Re: How could I have three differets field with specific information from Hire and leave days.

migueldfr_23 — Wed, 09 Oct 2024 07:08:09 GMT

Good morning @zar .

Thank you for your response.

I’m attaching an Excel example with the logic behind the script provided.

Additionally, I would like to add the option to select by date or by month and compare them, depending on the key user’s preferences.

The display should include:

P1 canonical date or month
P2 canonical date or month

Then, I’d like to compare them with a percentage showing the gain or loss.

I would appreciate any help

Re: How could I have three differets field with specific information from Hire and leave days.

zar — Wed, 09 Oct 2024 16:48:06 GMT

Few considerations:

1) For comparison analysis consider use Alternate States.

2) Simplify your approach, use IntervalMatch for dates, for example, or Master calendar.

3) Use your master calendar fields for filtering.

4) Create Filter pane in default and alternate state for comparison. Same for KPI. The easiest way to have it.

5) Use alternate dimensions in charts to see analysis at distinct level.

This is an interesting project, I highly recommend view some QCC courses related to work with dates and generation of calendars.

My example view:

Expression for KPI is exactly the same for both, only changes the state they are:

Expression: Count(Distinct person_id)

State for second KPI and Filters:

Providing the script used:

hcm_workers: LOAD person_id ,Date#(start_date,'YYYY-MM-DD') as start_date ,Date#(termination_date,'YYYY-MM-DD') as termination_date ; LOAD * INLINE [ person_id, start_date, termination_date Employee01,2024-08-25,2024-09-01 Employee02,2024-09-01, Employee03,2024-08-30,2024-09-26 ] ; TEMP_ALL_POSSIBLE_DATES: LOAD Distinct start_date AS DATE_FOR_CALENDAR RESIDENT hcm_workers ; Concatenate(TEMP_ALL_POSSIBLE_DATES) LOAD Distinct termination_date AS DATE_FOR_CALENDAR RESIDENT hcm_workers ; Concatenate(TEMP_ALL_POSSIBLE_DATES) LOAD Distinct Date(Today(1),'YYYY-MM-DD') AS DATE_FOR_CALENDAR AutoGenerate 1 ; // calendar generation https://qlikviewcookbook.com/2015/05/better-calendar-scripts/ MasterCalendar: Load TempDate AS Date, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, 'Q' & ceil(month(TempDate) / 3) AS Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, WeekDay(TempDate) as WeekDay ; //=== Generate a temp table of dates === LOAD date(mindate + IterNo()) AS TempDate ,maxdate // Used in InYearToDate() above, but not kept WHILE mindate + IterNo() <= maxdate; //=== Get min/max dates from Field ===/ LOAD Min(FieldValue('DATE_FOR_CALENDAR',recno()))-1 as mindate, Max(FieldValue('DATE_FOR_CALENDAR', recno())) as maxdate AUTOGENERATE FieldValueCount('DATE_FOR_CALENDAR'); drop table TEMP_ALL_POSSIBLE_DATES; //Intervalmatch bridge (can join if dont wnt sync key) Inner Join (hcm_workers) IntervalMatch(Date) LOAD start_date ,termination_date RESIDENT hcm_workers ;