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.

[Kushal_Chawda]

what is the expected output?

[gogreen]

I merged two individual requirements in the request here instead of posting multiple times in the community.

The expected outputs are the percentage (KPIs) for each requirement.

Requirement 1:  (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 flag condition) / (count of workers who strictly belong to combinations of A,B,C,D,E but not any of F,G,H,I,J with flag condition)

Requirement 2: (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 flag condition)/ (count of 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 flag condition)

Hope this is clear also may be the sample data file attached will help as I calculated the possible numbers manually for both  KPIs using the dummy records.

Thanks!

[Kushal_Chawda]

@gogreen  expected output with numbers will be helpful

[Ksrinivasan]

Solution for First Requirement :  =count({$<[Worker id]= {'1'}>}Flag) / count({$<[Worker id]= {'2','4','5'}>}Flag)

Solution for Second Requirement :  =count({$<[Worker id]= {'1'}>}Flag) / count({$<[Worker id]= {'1','6','7','8'}>}Flag)

[gogreen]

@Kushal_Chawda  Attaching here the snapshot of the expected output for the sample data. Hope this is what you meant.

@Ksrinivasan Thanks but in the real data I have some 90000 worker ids and the work types are always 10. Hence trying to find the automated way using work types rather than worker ids.

Sai.

[Kushal_Chawda]

@gogreen  how 3 is calculated?

[marcus_sommer]

If I understand your question right you need at least one AND linking between your conditions. Maybe something like the following is helpful:

count({< Type = {'A', 'B', 'C'}> - < Type = {'F', 'G', 'H', 'I', 'J'}>} Worker) 

- Marcus

[gogreen]

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.

(Small correction the right answer is 4 but not 3 for this calculation - my bad. Please ignore the above snap for this.)

Thanks.

[gogreen]

Thanks Marcus!

Doesn't this give me only possible worker ids of (only A,B,C but not 'F', 'G', 'H', 'I', 'J')

what I am looking for is (only A,B,C,D,E but not 'F', 'G', 'H', 'I', 'J') as denominator - say 'y' and

A,B,C with in 'y'- say 'x'. My final answer is 'x'/'y'.

Thanks,

Sai.