Count is not working correctly 1

[rjm13]

Hi all,

I have a report, where I am summarizing unique clients By household income. I am using 2 formula fields to do this.

The first:
if DateValue({?StartDate}-1)< DateValue ({ai_prog.start_dt}) and
DateValue ({ai_prog.start_dt})<DateValue ({?EndDate}+1)and
({ai_prog.initial}= True) then {prevention04.Tc_Id}

Yields instances of the client ID number if the conditions are met.

The second:

DistinctCount({@NewEnrollments},{@Householdncomes})

Simply is a distinct count of those client ID's totaled by the grouping @householdncomes (and is placed in the household income group header).

The problem that I am having is that when I look at the totals I find that it is always higher by one, ie. income under 10000 meeting the formula criteria should be coming out as 9 and it shows on the report as 10. It is the same for each of the householdncome groupings.

Any help would be greatly appreciated, as this is very frustrating, especially on a Friday!!!

Thanks

 

[lbass]

This is happening because all of the records that don't meet your criteria receive an Id of 0. When you do a distinctcount, the 0 value counts as 1 of the distinct values. You can't safely just subtract 1 from your distinctcount formula, as this will be inaccurate if there are times when all records meet your criteria. You could instead use a running total (using the editor), where you do a distinctcount of ID, evaluate based on a formula:

DateValue({?StartDate}-1) < DateValue({ai_prog.start_dt}) and
DateValue({ai_prog.start_dt}) < DateValue({?EndDate}+1) and
{ai_prog.initial}= True

Reset on change of group (Household Income). The running total would have to be placed in the group footer.

-LB

 

[rjm13]

Thanks LB, worked like a charm!