DistinctCount Problem

[ChiTownDiva]

Happy Friday All...

I'm having a heck of time with a distinctcount...

I need to count up the number of employees that are management versus non-management. I wrote two formulas to count them:

{@MgtCount}

If {mgt level} = 'M' then {pers_id)

I do the same for {@non-Mgtcount}.

The problem is when I go to do a distinctcount on {@mgtCount} it adds 1, so instead of Torey have 21 employees, it shows 22. Why is that?

I tried using running totals which works, but I need that final value of 21 in the group header/footer.

Any ideas.

Thanks in advance for your help.

ChiTownDivaus

 

[Reebo99]

2 Formulas :

{@MgtCount}
If {mgt level} = 'M' then 1 else 0

{@non-Mgtcount}
If Not({mgt level} = 'M') then 1 else 0

place both fields in the detail, right click, insert, summary and sum the field. You can now delete this from the detail, and you can move the summary field wherever you want.

Note, if you put this field in a group header/footer it will show a result for that group. If you put this in the Report Header/Footer, it will show the result for the whole report.

Let me know how you get on.....

Reebo
Scotland (Sunny with a Smile)

 

[ChiTownDiva]

Thanks Reeb099...

I changed the formula and it still comes back with an extra record (16 non-management people instead of 15).

Any other ideas?

Thanks.

ChiTownDivaus

 

[jadzian]

I gess what is wrong.
You use formula :
If Not({mgt level} = 'M') then {pers_id)
This formula gives you sometimes {pers_id} sometimes null. DistinctCount counts this Null as 22nd employe.
On the other hands, I don't understand why formula
If Not({mgt level} = 'M') then 1 else 0
gives You wrong result. It should work correctly.

Jadwiga

 

[ChiTownDiva]

Thanks Jadzian...

I changed the formula and I'm still getting an extra body...

ChiTownDivaus

 

[jadzian]

Could You send mi this report.
My e-mail: jadwiga.nowak@acn.waw.pl

Jadwiga.

 

[lbass]

I think the problem is that you are using a distinct count on a formula that is also counting the default value. If you have results like this at the detail level:
{@Mgtcount} Distinctcount RT
det1 146 1 1
det2 0 2 1
det3 236 3 2
det4 0 3 2
det5 357 4 3

A distinct count of {@Mgtcount} would total 4, 1 for each ID plus 1 for the "0" for non-management. If you do a running total using distinct count, but specify that {Mgtcount} = "M", then it will only count the three IDs.

If your goal is only to count management staff, then change your formula to:

if {mgtlevel} = "M" then 1 else 0 //as Reebo suggested

And then SUM the results--do not use count or distinctcount.

-LB

 

[jadzian]

None of this resolves above is correctly.
Previous I explain why uour first formula can't calculate good result, now I understand why Reebo's formula is wrong.

You use DistinctCount, not Count, so I recognize You join two tables, due to some of {pers_id} are dupplicated. If You use formula
if {mgtlevel} = "M" then 1 else 0
and then the SUM You won't calculate "distinct" {pers_id}.
In some chance You can obtain good result, but not always.
I think, You have to use runnig total. In my opinion, You haven't any choise.

Jadwiga.

 

[lbass]

I agree that if there are duplicates you need to use a running total--although I assumed you might have tried using distinctcount BECAUSE initially you were getting an extra count, not because you knew you had duplicates. So are there any duplicates?

-LB