Set Analysis

[Rabia_Taimur]

I am using below code to get number of employees hired in current fiscal year. This data is filtered over departments in the dashboard. If a department has no hirings in current fiscal year, it shows hirings for the previous year. I want to show 'Zero (0)' if there are no hirings for any department filtered in current fiscal year. Appreciate the usual support i get from the community.

Count
(
DISTINCT
{$
< Joining_Dt={">=$(=DATE(MAX(YearName(Joining_Dt,0,7))))"},
NonHiring_N -={'1'},
EmpTypeText-={'Trainees MPCL','MPCL Consultants'}
>
}
Emp_No
)

[L_VN]

If you're looking to do lots of these types of HR related follow ups I'd suggest you implement a better back-end model where everything's linked to the calendar.

[diegozecchini]

Hi!
To ensure your expression returns 0 when there are no hirings in the current fiscal year, you can modify your Set Analysis by incorporating an explicit IF condition or using Qlik's handling for null values. Here's how you can adjust your formula:

Updated Expression:

IF(
Count(
DISTINCT
{$
<
Joining_Dt = {">=$(=DATE(MAX(YearName(Joining_Dt,0,7))))"},
NonHiring_N -= {'1'},
EmpTypeText -= {'Trainees MPCL', 'MPCL Consultants'}
>
}
Emp_No
) > 0,
Count(
DISTINCT
{$
<
Joining_Dt = {">=$(=DATE(MAX(YearName(Joining_Dt,0,7))))"},
NonHiring_N -= {'1'},
EmpTypeText -= {'Trainees MPCL', 'MPCL Consultants'}
>
}
Emp_No
),
0
)

[Rabia_Taimur]

Below code worked for me, Thanks for the idea!!!

Count
(
DISTINCT
{$
< Joining_Year={">=$(=DATE(MAX(YearName(Today(),0,7))))"},
Joining_Year-={"<$(=DATE(MAX(YearName(Today(),0,7))))"},

NonHiring_N -={'1'},
EmpTypeText-={'Trainees MPCL','MPCL Consultants'}
>
}
Emp_No
)

[Rabia_Taimur]

Thank you for the response, i am almost at the final stage of my dashboard. Its a big challenge working with multiple slowly changing dimensions. presenting them on 1 timeline becomes a big big hassle. I am new to qlik still learning. Would love to learn more on Date type dimension handling in Qlik load editor

[diegozecchini]

Hi! I am glad to be helpful, have a nice day