Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Bruno_D2BI
Partner - Contributor III
Partner - Contributor III

SET ANALYSIS With COUNT to have o subquery in a year

Hello everyone,

I use QLIK SENSE Server in Windows with the version February 2023 Patch 5.

I need to calculate an indicator allowing to know the number of people present over a whole period without interruption. For this, I used a SET ANALYSIS to calculate for each employee (key KEY_SALARIE) how many months over the year they are present out of the 12 expected. I then count the distinct number of people only for those who are 12/12 months old.


COUNT({<$(v_SET_Type_Fait_KPI),$(v_SET_M),$(v_SET_Contrats_CDI)
,[% Temps Partiel]={">=1"}
,FLAG_SAL_PERMANENT={'1'}
,FLAG_EFFECTIF={'1'}
,[Règle Exclusion ETP Sécurité Sociale]={'0'}
,KEY_SALARIE={"=$(=COUNT({<
$(v_SET_Y)
,$(v_SET_Contrats_CDI)
,[% Temps Partiel]={">=1"}
,FLAG_SAL_PERMANENT={'1'}
,FLAG_EFFECTIF={'1'}
,[Règle Exclusion ETP Sécurité Sociale]={'0'}
>} DISTINCT [Date Analyse]))=12"}>} DISTINCT [KEY_SALARIE])


This unfortunately doesn't work. Indeed, some employees who are present only 10 months or 11 months are wrongly counted and I do not understand why because when I select this person, the result of my indicator puts 0. 
But without employee selection, the value of the same KPI for the same person is 1.


The variables :
$(v_SET_Y) -> Take all periode of the year even if they are a selection of a month.

$(v_SET_M) -> Take the last month in the period to count the person in this month

$(v_SET_Contrats_CDI) -> Take only types of contract

Would you please have an idea?

Thanks in advance !!

Regards, Bruno

 

Labels (1)
10 Replies
Bruno_D2BI
Partner - Contributor III
Partner - Contributor III
Author

Thanks a lot it's working !!! 🙂

Thanks for the explainations it's clear 😉

Have a good day 

Regards,
Bruno