topic Re: Set Analysis Syntax in App Development

Set Analysis Syntax

gogreen — Fri, 08 Jan 2021 15:38:01 GMT

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.

Re: Set Analysis Syntax

Kushal_Chawda — Wed, 06 Jan 2021 22:20:58 GMT

what is the expected output?

Re: Set Analysis Syntax

gogreen — Wed, 06 Jan 2021 22:54:37 GMT

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!

Re: Set Analysis Syntax

Kushal_Chawda — Thu, 07 Jan 2021 00:02:51 GMT

@gogreen expected output with numbers will be helpful

Re: Set Analysis Syntax

Ksrinivasan — Thu, 07 Jan 2021 06:34:00 GMT

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)

Re: Set Analysis Syntax

gogreen — Fri, 08 Jan 2021 18:24:36 GMT

@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.

Re: Set Analysis Syntax

Kushal_Chawda — Thu, 07 Jan 2021 23:22:31 GMT

@gogreen how 3 is calculated?

Re: Set Analysis Syntax

marcus_sommer — Fri, 08 Jan 2021 13:24:07 GMT

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

Re: Set Analysis Syntax

gogreen — Fri, 08 Jan 2021 18:18:56 GMT

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.

Re: Set Analysis Syntax

gogreen — Fri, 08 Jan 2021 17:56:34 GMT

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.

Re: Set Analysis Syntax

marcus_sommer — Mon, 11 Jan 2021 12:12:22 GMT

It wasn't meant as a full solution to your question else just as an idea to the kind of the conditions. To pick up a subset of data which restrict data from another limited subset is a rather straightforward approach but to create a product of multiple subsets could become more complicated.

- Marcus

Re: Set Analysis Syntax

Ksrinivasan — Mon, 11 Jan 2021 14:40:29 GMT

hi,

Solution 1: =count({$<[Work type]= {'A','B','C'},[Worker id]= {'2'},[Flag]= {'1'}>}Flag)

Solution 2: =count({$<[Work type]= {'A','B','C'},[Worker id]= {'1','6','7','8'},[Flag]= {'1'}>}Flag)

regards,

ksrinivasan,

Re: Set Analysis Syntax

gogreen — Mon, 11 Jan 2021 17:07:23 GMT

Got it. Thank you!

Ya, I think this is the case of multiple subsets.

Sai.

Re: Set Analysis Syntax

gogreen — Mon, 11 Jan 2021 17:08:50 GMT

Thanks!

But I think the solution should be without mentioning [worker id] at all in the filters inside as there are a lot of them.

Thanks,

Sai.

Re: Set Analysis Syntax

Ksrinivasan — Mon, 11 Jan 2021 17:51:05 GMT

Hi, in your derivation you have mentioned worker I'd. Will you derive without Id and post that example.

Ksrinivasan.

Re: Set Analysis Syntax

gogreen — Wed, 13 Jan 2021 18:19:31 GMT

Hi,

I mentioned worker ids with respect to work types. Was showing an example of what rows qualify for each calculation. Do you think sample data from the file and my initial description might help?

Thanks,

Sai.