Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
;
