Graph using percent, not totals

[shelby55]

Hi

Again, the search feature isn't working so I apologize if there is a thread to answer my question.

I am using CR 9 on a Windows XP (SP 2) computer accessing an Access XP database.

I'm sure this is just a brain blip on my part but here goes: I have a report where I want to create a stacked bar chart for discharges before 11:00 and discharges on or after 11:00. So I have two formulas which work:

If Time({Data.DisTime}) in Time(11,01,00) to Time(23,59,00) then +1

If Time({Data.DisTime}) in Time(0,00,00) to Time(11,00,00) then +1

I want this information to change based on change of fiscal year (user has to input two timeframes) with the fiscal year formula as:

If({Data.DisDate}) in Date(2003,04,01) to Date(2004,03,31) then "Fiscal 2003/04" else
If({Data.DisDate}) in Date(2004,04,01) to Date(2005,03,31) then "Fiscal 2004/05"

This works fine when just wanting totals but I want %. However, I can't show as % with formula above because CR9 only allows me to show Over 11:00 as % of total Over 11 or Before 11 as % of total Before 11 and I want to show % as total of all cases per timeframe.

I created a total cases formula:
If Not IsNull({Data.ChartNo}) then +1 else 0

and then created two % formula:

Sum({@Disp After 11})*100/Sum({@TtlCases})
Sum({@Disp Before or at 11})*100/Sum({@TtlCases})

However, when I use those to graph on, it shows for all cases combined and doesn't change on change of fiscal year. What am I doing wrong?

 

[shelby55]

Hi

Sorry but I figured it out. I changed the summary of the two original formulas from sum to weighted average and used the formula of TtlCases as the field to be weighted average of.

However, I still don't really understand why the original % formulae didn't work but I've got what I need so I won't sweat it!

Shelby

 

[Madawc]

Do you use running totals? I'd suggest you write an experimental report and get familiar with them. They can do selective counts much more conveniently.

Madawc Williams (East Anglia, UK)