Crystal Report help

If "average" is not showing up, it doesn't sound like the field your trying to average is a numerical value. Possibly it's a text representation of a number?

If so, try creating a formula with tonumber(amount of staff logged in), and then do a running total on that. You should be able to average that.

Let me know if that works for you!

That doesn't work.

The report is grouped by logon date, Time of Day(hour) then Staff.  This lists the staff logged on at that Date and Time.  I have the running total counting the number of staff logged on to reset on change of Time of Day.(hour)  This is where I would like to create a formula to average the count of staff logged on at each hour for each day the report was ran for. Hope this explains it better
Thanks

Hmmm...

If you do a running count of staff, resetting per day, and do a running count of hours in that day that have staff logging into the system, and then have a formula that divides the number of staff by the number of hours, I'm thinking that should work. You can have the average print in the GF of the hour, and it'll give you a running average, and put it in the GF of the day, and it'll show the average for the day.

Let me know if that doesn't make sense... :)

I don't know how I would do a running total (count) of hours in the day staff is logged on.  That sounds like it would have to be a formula before even attempting the other things.  

That's what I get for trying to re-create what you're doing with my data... I was fudging the "hours of the day" grouping, and I was counting the individual items in that group and calling them "hours", but yours can't, because Crystal is grouping them internally by hour like that.

Let me chew on this...

Ok Thanks:o)

I'm not sure what you're trying to do.  If the day only had two hours when someone was logged in, and 10 people were logged in during one hour and 5 people during the other hour, are you looking for the average of those two counts, which would be 7.5?

 If so, then I think ZebulonPi has the right idea, but instead of trying to do a running total of the hours, I'd use a variable that was incremented in the "hour" group footer.  Then you'd divide your login running total by that variable.  Let me know if you need more details.

 James

I'm a beginner so I'll need more detail please.

Make a formula called counter set, with this in it:

shared numbervar counter := 0;

and put it in the logon date group header, so it resets on the date.

Then make a formula called counter with this in it:

shared numberVar counter;
counter := counter + 1;
counter

and put it in the "hour" group footer. This will count the hours for you, per day, that someone logged in.

Now, just divide the running total of people that logged in by the counter, and bam! Average logons!

I will try this and let you know, Thanks

I tried the above and it's calculating something but it doesn't look righ to me. For example:
At 5pm on 2/19 there were 6 staff logged on.  On 2/20 at 5pm there were 4 Staff logged on.  After trying the above I am getting two answers one for each day which are .50 and .33.  This is not right.  I need onw average for each time of day.  Which using the above should give me and average of 5 Staff logged on a 5pm for 2/19 and 2/20.  I'm starting to think this isn't possible, unless I've done somethign wrong??? I did follow the instruction though

I said that I wasn't sure what you were trying to do.  :-)

 I thought you wanted an average of the people logged in over the course of one day, so you just needed to add up the people logged in each hour that day and get a count of those hours, then divide to get the average.

 But apparently you're looking at the number of people logged in at different times over the course of more than one day and trying to get a separate average for each hour for those days (average number of people logged in at 1, at 2, etc.).

 The simplest way to do that would be to have the report grouped by time first, not date, so that all of the logins for a given hour, for every day, were together.  But I'm guessing that you don't want to do that.

 If I'm right about all that, you'll probably need formulas to accumulate login totals for each hour, and maybe the number of days that each hour had logins (If a day had 0 logins during a particular hour, should that be factored into the average?), and then you'd use those saved values to calculate the average for each hour, presumably in the report footer.

 How many hours are we talking about?  Something like 9 - 5 or ...?

 How many days?

 James

That is more in the area of what I'm trying to do. Unfortunatley, there is no set hrs of the day, because people can also work from home as well.  Some days there's a logon as early as 6am, and as late as 10 pm, and other days it could be totally different.  As far as 0 logons within a particular hour on one day and logons at that same hour on a different day, yes that would have to be added into the average for that hour. This is a tricky one, I will, if I have to,  group the report by Hour then by Day, if this is the only way to make it work.  So what would be the next step?

Thanks

Forgot to tell you that the report will be ran for up to two weeks at a time for the most part, is this something that has to be set in stone?  What if someone wanted to run it for more than that?

I think you ARE going to have to group by hour first, and then by day. Just put in parameters for begin date and end date, group by hour, do your running count of employees (not resetting) and your count of days. In the hour group footer, divide the number of employees by the hour count, and you'll have your average.

This has the benefit of being able to be run for any date range, and, if there are strange login hours, it'll catch them. If no one logs in for a particular hour, then it won't be on the report, so you don't have to code for it special or anything.

I can go into more detail if you need me to.

Ok, I tried that and it is not pulling a correct average.

So, you've got the group by hour, and then, inside that, you have the group by date, and you're counting up the logins inside each date, and the counter is clicking by the dates, adding up every date that has some logins in it... that look like it's all working OK?

What's wrong with the average? What's it coming out with at the end?

I have it grouped by Hour, then by Date, then by Staff Name.  The running total for all staff in the report footer and running total of days in the report footer. Then in the Group footer for the hours I have a formula that divides the running total of Staff by Running total of Days.  The nubers are off

In the Date group, did you change the "The section will be printed" option for that group to "for each day"?  In my experience, it defaults to "for each week".

 How do you count the days?

 How do you count the staff?

 Do you have one record for each person logged in during a given hour on a given day, or something else (like login and logout records)?


 FWIW, I think that this could be done with the report grouped by date first.  I think it will be much easier if you group by hour and then date, but I think it could be done the other way.

 James

I do have the report pulling for each day and each hour.  I have changed the report to group by date first then hour then staff.  I am doing a running total for a distinct count of staff to reset with change of "Hour" Group.  I am pulling the data using logon date/time.  

You had it by Hour then Day before.  Do you prefer to group by Day and then Hour, or did you change it for some other reason?  As I said before, I think it would be much easier if you grouped by Hour and then Day, but I think it could be done with the groups the other way around.  I just want to make sure that that's what you want before I head down that path.

 James

I prefer to group by day first. But if it would be easier I can switch it back.

A small correction:

 In that last paragraph, I said:

 > Also, I set the reports not to show the hours when the count was 0, ...

 That should be "formulas", not "reports".

 James

Alright I'll try this and let you know what happens

I'm a bit confused by this:
Replace {your hour group field} with the field that you're using for the Hour group.
 Replace {#staff running total} with your running total that counts the number of staff logged on each hour


What am I replacing them with if you can be more specific, thanks

Never mind I got it, I understand that you were explaining the above formula-sorry

Thank You it worked!!!!

Thank You Very Much I've been trying to get this to work for what seems to be ages, you're great!

You're welcome.  Glad I could help.

 James