Percentage of individual records higher than a set number

[Anonymous]

HI,

I wonder if someone can help me. I have a table that looks like:

StaffID Type

1          a

1          a

2          b

2          b

2          b

3          c

3          c

I want to show percentage of staffID with more than 5 records, so I have used the below formula:

Count(distinct(if(Aggr(count(StaffID),StaffID)>5,StaffID)))/count(distinct(StaffID))

Then staff with less/ equal records number to 5:

Count(distinct(if(Aggr(count(StaffID),StaffID)<=5,StaffID)))/count(distinct(StaffID))

but both percentages don't add up to 100%, these are always lower around 92% as I am showing the % by type.

What am I doing wrong?

Thank you in advance for your help.

[sunny_talwar · Accepted solution]

May be try this

Count(DISTINCT {<StaffID = {"=Count(StaffID) > 5"}>} StaffID)/Count(DISTINCT StaffID)

and

Count(DISTINCT {<StaffID = {"=Count(StaffID) <= 5"}>} StaffID)/Count(DISTINCT StaffID)

[Anonymous]

Thank you very much Sunny