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

[migueldfr_23]

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 

[zar]

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.

[migueldfr_23]

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

[zar]

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
;