topic Remainder Function in set analysis with aggregation in QlikView

Remainder Function in set analysis with aggregation

shanky1907 — Wed, 01 Nov 2017 09:48:51 GMT

Hello All,

I have to implement one expression where only those EMP should be counted who have completed all their assigned trainings, which means CompletedTopics/TotalTopics = 1 or TotalTopics-CompletedTopics = 0.

Now, i will tkae Count of EMP according to the condition put up in set analysis.

Also i want to aggregate the values with EMP as different Employees have different assigned number of Topics.

Here, my Dimension is Team.

So, the final count will represent the number of employees in that team who have completed all their asigned topics.

Thanks in advance.

Re: Remainder Function in set analysis with aggregation

Gysbert_Wassenaar — Wed, 01 Nov 2017 10:12:02 GMT

Perhaps like this: count({<EMP={"=sum(CompletedTopics)=sum(TotalTopics)"}>}distinct EMP)

Re: Remainder Function in set analysis with aggregation

shanky1907 — Wed, 01 Nov 2017 10:19:50 GMT

Thanks for the reply Gysbert.

But the expression is not giving correct result in Total.

Re: Remainder Function in set analysis with aggregation

Gysbert_Wassenaar — Wed, 01 Nov 2017 10:21:35 GMT

Please post an example that demonstrates the issue.

Re: Remainder Function in set analysis with aggregation

shanky1907 — Wed, 01 Nov 2017 10:25:16 GMT

REmoving the sum solves my issue:

count({<EMP={"=(CompletedTopics)=(TotalTopics)"}>}distinct EMP)

Thanks for the help.

Re: Remainder Function in set analysis with aggregation

sunny_talwar — Wed, 01 Nov 2017 11:16:45 GMT

You can further improve this by carrying out this calculation in the script

If(CompletedTopics = TotalTopics, 1, 0) as TopicFlag

and then this:

Count({<TopicFlag = {1}>} DISTINCT EMP)

Re: Remainder Function in set analysis with aggregation

shanky1907 — Wed, 01 Nov 2017 11:19:22 GMT

Yes, exactly i thought abou this approach also if couldn't get it rohgt on Sheet level.

Thanks for the input Sunny.