Running total

[Crystalguru]

Crystal V 8.5
Lotus Notes view

Group 1 = Category
Group 2 = Case ID

Running total that counts the caseids that fall within the current year.
CaseID ReportDate current year
123 5/6/2005 1
124 4/7/2005 1
234 5/3/2004 0
256 5/8/2005 1

Grand Total: 3

Here's my issue. The running total is working for the current year count and the grand total. But when I want to do a current year/grand total, the % is wrong.
CaseID ReportDate current year
HARRASSMENT
123 5/6/2005 1
124 4/7/2005 1
234 5/3/2004 0
256 5/8/2005 1
HARRASSMENT TOTAL: 3 100%
HOSTILE
323 5/6/2003 0
224 4/7/2005 1
234 5/3/2004 0
356 5/8/2005 1
HOSTILE TOTAL: 2 50%
Grand Total: 5
The grand total running total is being calculated at the same time the percentage, so when it calculates the Harrassemnt percentage, the grand total is at a count of 3 and so on...once the running total for the grand total gets to the end of the report, the last category has the correct percentage.
The correct % should be:
Harrassment: 60%
Hostile: 40%

Any ideas?

 

[Madawc]

A running total only shows the total so far. A summary total will take its total from the data before anything is printed, but it can't be selected. Which still doesn't fully explain your figures; you maybe should display the figures it uses for calculation as well as the result. But in any case, the method can't work.

The best alternative I can suggest is a grand total in the report footer, that totals qualified records for the group and for the whole report, using a parameter for the group value that you pass it. Not elegant or efficient, but it should display the correct figures, and could also pass them back to the main report using a Shared Variable.

Madawc Williams (East Anglia, UK)

 

[Crystalguru]

Right, the running total shows what is totaled so far. Rats. That's why I am getting 100% for the first value - it is 100% of the total so far.

I could do a Grand Total Sum on all the case ids, but I need a distinct count, there are some cases that appear more than once. And I still have to account for the year that the case is reported in.

For example:
Harrassment -
@current year count
123 4/5/2005 1
234 2/5/2005 1
234 2/5/2005 1
345 2/7/2004 0

The grand total distinct count on caseid would be 3. The sum on @current year count would be 3. It needs to be 2 for year 2005.

Thus my problem.

Any ideas?

 

[Madawc]

You can get the year using Year({ReportDate}) and group using that. But as I said, I think there may be no choice except a subreport.

Madawc Williams (East Anglia, UK)

 

[Crystalguru]

May have to go back to the drawing board on this one. It is already a subreport in a main report...

Thanks

 

[lbass]

Depending on your data, you might be able to concatenate the caseID with the date field, as in {@concat}:

{table.category} + totext({table.caseID},"000") + totext({table.date},"M/d/yyyy")

Eliminate the group on caseID and instead insert a group on date set for "Print on change of year", so that Category is group 1, and Year is group 2. Place {@concat} in the detail section and then insert a distinct count on {@concat} at the group and grand total levels. You can then do a summary formula at the year group level:

distinctcount({@concat},{table.date},"annually") % distinctcount({@concat})

While this is a formula at the group (date) footer level, you can drag it into the footer for category, and since the date group is ordered in ascending order, it will display the correct current year percentage in the category footer.

-LB

 

[Crystalguru]

Thanks LBass! Great thinking! I will give it a try and let you know how it goes.

 

[Crystalguru]

Wahoo! I altered LB's formula to bring back the caseid if the case fell into the date ranges I was looking for. If the case did fall into that range, I could do a distinct count summary on that field and only count the field once. Works great!

Example of my code (not exact):
If report_dt in ?startdate to ?enddate then caseid

Thanks for all your help.