topic Re: Using Count Distinct and Sum in QlikView

Using Count Distinct and Sum

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have tried to understand the answers for this question that have been posted previously but I am having trouble applying it.

Here is my dilemma. I would like to count distinct the engineers on a project and the sum the hours they have worked divided by 2080 (FTE). This would show up as a KPI. Basically a Productivity Matrix.

Engineers Actual Hours

John 100

James 600

mike 6

john 400

James 100

This is what I would like to get when I filter by engineer.

Total Engineers = 100%

John = 24%

James = 33.6%

this is what I have used - sum(aggr(sum(DISTINCT [Engineer]),[Actual Hours]))

Thanks

Re: Using Count Distinct and Sum

el_aprendiz111 — Tue, 06 Jun 2017 21:19:41 GMT

sum([Actual Hours])/2080

Re: Using Count Distinct and Sum

Anonymous — Tue, 06 Jun 2017 21:34:59 GMT

sounds like you really want

sum([Actual Hours])/sum(TOTAL [Actual Hours])

Re: Using Count Distinct and Sum

ahaahaaha — Wed, 07 Jun 2017 07:14:16 GMT

Hi,

If I understand you correctly, it can be as in an attached file.

Regards,

Andrey

Re: Using Count Distinct and Sum

vinieme12 — Wed, 07 Jun 2017 07:32:23 GMT

just incase , you might want to ignore filters on the denominator add {1}

sum([Actual Hours])/sum( {1} TOTAL [Actual Hours])

Re: Using Count Distinct and Sum

Wed, 07 Jun 2017 14:28:28 GMT

Thanks for the help, but this is what I am looking for, (My script writing it not the best)

sum(Count(distinct[Engineers],[Actual Hours])/2080

Basically:

John total hours a year he can work is 2080

He only worked 500 but they are spread out in 2 instances i.e. 100 and 400

How do I count one instance of John and sum the number of hours he worked.

Thanks again.

Re: Using Count Distinct and Sum

Anonymous — Wed, 07 Jun 2017 15:17:09 GMT

How about this?

Re: Using Count Distinct and Sum

NickHoff — Wed, 07 Jun 2017 15:31:54 GMT

You were close with your first attempt, but try

aggr(sum([Actual Hours])/2080,Engineers)

Re: Using Count Distinct and Sum

Wed, 07 Jun 2017 17:20:04 GMT

Hi Nick,

Thanks for the help. This Worked!!

Thanks also to everyone to jumped in.