SET ANALYSIS With COUNT to have o subquery in a year

[Bruno_D2BI]

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

[rubenmarin]

Hi, sorry but I didn't dully analyze it, and I'm not sure if I would have enough info, but my bet goes for the KEY_SALARIE in set analysis, I would try to add a dimple table with KEY_SALARIE as dimension and =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])

as measure, and check if it returns 12 when selections are made. If it reutnrs 12 add the other dimensions to check which one doesn't returns the expected value when person is selected.

[Bruno_D2BI]

Hello thansk for this answer but is not simple because when a user select a perimeter (an enterprise or multiple enterpreses) we need to now for this perimeter if they are 12 or 11 month. 

If we do this into a table in the script, we lost this precision.

Regards,

Bruno

[rubenmarin]

Hi, the idea of the table is only to have a tool that helps you to detect which part of the set analysis is excluding the data, causing the indicator to show 0.

With that detection you/we can focus on solve that particular part of the expression.

[Bruno_D2BI]

Hi,

I have do the simple table here is the result , for the person 00012824 whe have 10 in nb Mois (Nb Month) you can see this in the table below and in the top table with the mesure complete where we see 1 into Eff Permanent.

When I select this person only , in the same tables the mesure show 0 (it's the rigth value)

Thanks a lot for your help.

Regards,
Bruno

[rubenmarin]

Hi, if nb Mois is the count DISTINCT of [Date Analyse] included in set analysis, I don't know why it counts 1 when nothing is selected, teh set analysis should exclude that KEY_SALARIE as it doesn't has 12 months.

This is hard to check without the app to do some test, the idea is to try to replicate the expression splitted in different columns to check what's the cause.

[Bruno_D2BI]

Hi,

I understand, I have cut the expression 

The column Nb Mois Eff Per is  

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])

The second is 

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'}
>} DISTINCT [KEY_SALARIE])

We can see that this two values are normal whithout selection and with a selection (the values don't change)

The problem is with the KEY_SALARIE into the SET ANALYSE, I don't undersand.

[rubenmarin]

Hi, I don't see it either, as I see even without person selection the expression should return 0.

There should be something in the data model that causes the KEY_SALARIE to return 12 in some cases and when the person is selected it is reduced to 10, but I the values on the tables doesn't tell that.

More test can be done reducing the data loaded, with less data is easier to check what is doing. There should be a reason for this behaviour, maybe some synthetic keys in data model is causing data interferences.

I don't know if you can upload the app with some sample data to test when I have enough free time to check it.

[Bruno_D2BI]

Hi,

Thanks a lot for your help.

I confirm in the complete model they are not synthetic keys.

You can see here an application with the model (simplyfied and anonynised) with the data.

I have put in yellow the person in with the problem, the value of their identification number is 00012824

Thanks a lot for your help.

Regards,

Bruno

[rubenmarin · Accepted solution]

Hi, the $(=Count...) is calculated before the table, not by row, so it uses the overall data which returns 12 different months,  to do it by each KEY_ANALYIS you need to remove the $-expansion.

This leads to another issue as there is also double quotes in the [% Temps Partiel] field of the count set analysis, the best solution would be to flag all those >=1 with a simple one to avoid the need of double quotes.

A workaround could be to use the double-double quotes as a escape character:

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])