Set Analysis Syntax

[gogreen]

Hello Qlik fam!

I am having some technical difficulty with the syntax of set analysis. I am trying to get the count of possible values on top of already calculated possible values universe.

For example there are 10 workers in a factory who can get a different type of work to do every day and every worker can get any possible work type to do. Now the requirement is to get the count of workers who are purely tied with certain work types but not the other ones

Let the work types be

A,B,C,D,E,F,G,H,I,J where A,B,C,D,E,F belong to Category 1 and E,F,G,H,I,J belong to Category 2

Requirement 1: To get the count of workers with work type A,B,C from already calculated workers who strictly belong to combinations of A,B,C,D,E but not any of F,G,H,I,J with some flag condition (Flag value is specific to worker irrespective of work type and work day and should be considered for both numerator and denominator).

Requirement 2: To get the count of workers with work type A,B,C from already calculated workers who belong to both A,B,C,D,E and F,G,H,I,J sets overall but not just within the subsets of A,B,C,D,E or F,G,H,I,J separately with some flag condition (Flag value is specific to worker irrespective of work type and work day and should be considered for both numerator and denominator).

Attaching the sample data file with dummy data and .qvf file with what I have as of now here.

Any help is appreciated. Thanks.

Sai.

[jonvitale]

Here's one of your expressions (next time you should paste that here so that folks can try to understand without having to download).

count( 
{$< [Worker id]= p({$<[Work type]={'A','B','C','D','E'},[Flag]={'1'}>} [Worker id])
-
p({$<[Work type] ={'F','G','H','I','J'},[Flag]={'1'}>}[Worker id])>} [Worker id])

Really quickly looking at your expression syntax you are using Count(.... [Worker id]),

I think you want to use Count (distinct [Worker id]) otherwise you'll be counting individual workers multiple times.

[gogreen]

Thanks for considering @jonvitale !

Sure. The goal is to get the count of worker id instead of distinct worker id. The KPIs I shared in the .qvf file file are just the denominators of my each KPI calculation. I got stuck with calculating the numerators for both the KPIs.

Hope this is more clear.

Considering the dummy data

Requirement 1 Numerator: If you look at the worker ids and their work types only 2,3,4,5 has work types with in the subset of (A,B,C,D,E) .
And now among 2,3,4,5 only 2,3 has final expected work types with in A,B,C. Since 3 is having flag condition '0' only worker id 2 is the correct expected output.
Since worker id 2 has 3 records the final numerator value is 3.

Requirement 2 Numerator: Among all the worker ids only 1,6,7,8,9,10 are having values from both category 1 (A,B,C,D,E) and category 2 (F,G,H,I,J)
but not only in one of those categories. 9,10 are ruled out because the flag is '0' and there are 4 records 1-A,6-A,7-B.8-C with the combinations within A,B,C.
so the numerator is 4.

Correct answers considering the dummy data below.

Thanks,

Sai.