Average Excluding Zero

[newbie0423]

Hello
When doing a running average or just average it there a way to exclude 0's that are in details..
I have a report that I am summing the number of rooms that are running. For every hour it either counts it as a 1 if there was a case for that hour or a 0 if there was not. At the group footer I have the sum. I would like to average that sum without including the 0's. I have tried using a running total summing the field and evaluating with a formula (see below)but this doesn't seem to be working as it returning the total and not the average. What am I doing wrong, please help. Using Crystal XI

not (
isnull({@4-5hour}) or
{@4-5hour} = 0
)




Thank you
V

 

[pmax9999]

If the RT is returning a Sum, you need to change the Running Total Type of Summary from Sum to Average.

Cheers
Pete

 

[newbie0423]

Hi Pete!

The RT is set to average, but it is returning 1. This is the same report that you helped me with in
thread767-1715296. It is doing an average counting all of the detail lines instead of only those that have a one value. For instance:
it is (1+0+1+1+0)/5
I want it to be (1+0+1+1+0)/3

 

[pmax9999]

Hey Newbie0423


If the result of the {@4-5hour} formula is either 1 or 0, the average of all those not equal to 0 will always be 1.

In your example above you say you expect the result (1+0+1+1+0)/3 which is 1, yet you say the Average RT is returning 1 in error.

Sorry, I'm confused again. You are going to need to explain it more simply for me.

Cheers
Pete

 

[newbie0423]

Ok, I'm sorry I'm doing such a crappy job of explaining this. I've been tasked to get the average number of rooms running per hour for each day. I am running the report for three months. If a case was running in a particular hour a 1 populates, if not it's a 0. There could be a total of 68 rooms running in a particular hour for that day. I want to get the average of that 68 for that day. My report is grouped on day of week. Thank you Pete.
Monday 3:00
1242-1635 1
1029-1119 0
1533-1545 1
1207-1530 1
1000-1100 0
0900-0945 0
1515-1545 1
1550-1700 1

 

[Charliy]

If you're summing the ones and zeros, and only want to average the ones, won't the answer always be 1?

 

[SkipVought]

sum the [Calculation of the DURATION for each event multiplied by the 1 or 0], and divide by [the sum of 1 or 0].

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, the averabe for the given example is 1:49:36


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pmax9999]

Your most recent post just adds to my confusion to be honest. For example, I don't understand the reference to the various time periods as I understood (based on the earlier thread) that the whole basis of the report was to determine the room usage for each hour of the day.

Based on my understanding of the report, assuming you are still using the code I suggested in the earlier thread, and that the report is being run for a single day, you will finish up with 24 totals that give you the total rooms in use for each of the 24 hourly periods. If you add those results and divide that by 24 it will give you the average number of rooms in use across each hour of the day.

If you are running the report across longer than a day, you would need to be also grouping by day and the formulas in the RF would need to be moved in the GF.

Or have I misunderstood completely what you are trying to do?

Hope this helps.

Cheers
Pete

 

[newbie0423]

Hi Pete,
I am still using the code that you gave me in the previous thread. I was running the report for one day, but now I am running the report to get a months worth of data. I do have the report grouped by day.In the running total I have the field to summarize @3-4hour, evaluate on change of group
reset never. For the 3-4hour for the month of August there were 49 rooms running at that time. I want to find the average of 49.

Val

 

[pmax9999]

So, are you attempting to:

1: Average the results of all hours across a single day (average number of rooms in use for a given day); OR
2: Average the results of a single time period across the days covered by the report (average number of rooms in use between 20:00 and 21:00 for the month).

What do you mean when you say you "... want to find the average of 49 ". Surely Average(x) = x (ie, the average of any number is simple that number).

On the assumption the answer to the 1st question above is 2, amend the final formula I provided (the one I said to put in the RF, but which should now be in the GF for the date group) as follows"

WhilePrintingRecords;
Global NumberVar T20_21;
Global NumberVar RT20_21 := RT20_21 + T20_21;

T20_21

Then, the average of all of the 20:00-21:00 hours across the month can be calculated by dividing the value of the RT20_21 variable by the number of days in the period of the report. Assuming the report period is determined by a date range parameter, that formula would look something like this:

[Code Average20_21]
WhilePrintingRecords;
Global NumberVar RT20_21;

RT20_21 / (Maximum({?Date_Range}) - Minimum({?Date_Range})+ 1)
[/Code]

Obviously, you would need to create a similar formula for all hourly periods (so a total of 24 formula), amending the variable names for each. The calculation on the number of days may need to be amended depending on how the report is determining the date range to be covered.


Hope this helps. If this is not what you are looking for, I am not sure I can provide any more assistance unless you give a much better explanation of the report structure and what you are trying to achieve.



Regards
Pete

 

[newbie0423]

Thanks for your help Pete. I apologize for the vague examples. Your second description explains what I'm trying to accomplish best. When I used the second formula I got an error message that "this field can not be summarized". At this point I don't know what else to do . You have given me the solution, but it's just not working for me.

Thanks again for all of your assistance!

 

[pmax9999]

That error message occurs when the parameter is not set to allow for a range. As I said in my post my solution assumes the report uses a date range parameter for the date selection. If that is not the case, the formula would fail with the error quoted.

Please explain how are you determining the date range within the report, ie:
> 2 params (start and finish);
> hard coded dates;
> some other approach, such as month/year params.

In explaining, please be specific.

Cheers
Pete

 

[newbie0423]

Hi Pete,

I am determining my date range by using two parameters. StartDate and EndDate.They are set up to not allow for a range.

 

[pmax9999]

In that case, amend the second formula above as follows:

[Code Average20_21]
WhilePrintingRecords;
Global NumberVar RT20_21;

RT20_21 / ({?Date_Start}) - ({?Date_Finish}) + 1)
[/Code]

Cheers
Pete

 

[newbie0423]

Hi Pete,
I tried using the formula but I got an error message saying that a "number is required here" {?StartDate} is highlighted.

 

[pmax9999]

Sorry, that should have been:

[Code {@Average20_21}]
WhilePrintingRecords;
Global NumberVar RT20_21;

RT20_21 / ({?Date_Finish} - {?Date_Start} + 1)
[/Code]

Cheers
Pete

 

[pmax9999]

And I probably should have used the parameter names you gave me - so it would look like this:

PrintingRecords;
Global NumberVar RT20_21;

RT20_21 / ({?EndDate} - {?StartDate} + 1)

Pete

 

[newbie0423]

Hi Pete,
I'm not getting any error message, but I'm getting 0 as the results. This is what I have: In the GF:
WhilePrintingRecords;
Global NumberVar RT15_16;
RT15_16 / ({?End Date} - {?StartDate} + 1)

and in the RF


WhilePrintingRecords;
Global NumberVar T15_16;
Global NumberVar RT15_16 := RT15_16 + T15_16;
T15_16

I amd using a months worth of data. the formula in the the RF is working it is counting 245 records for 3:00 hour, but shouldn't the formula in the GF give me the average? As always Pete, thank you for taking time to help me! I really appreciate it

Val

 

[newbie0423]

Hi Pete,
I've decided to do my calculations manually. this is causing me way too many headaches. Thanks again for all of your help, you've been great......ttyl )