Add a basic count field to my report. 2

[urbanhim]

This is a really simple request, but I cant seem to do it.

I am building a report based on Orders recieved per minute. I then need to add a graph which shows both Total orders per minute, and average orders per minute.

The total orders i'm ok with, i have a Sum of ORDER_HISTORY_ID however, I cant seem to do average as ORDER_HISTORY_ID is a number, so it returns an incorrect average. It wont let me do a Average of the count sum i have either.

Therefore id like a formula field that simply counts each record and adds a 1 each time. Then i'm hoping that i can then add a sum which will show "Average" per minute.

Ive tried this:

COUNT ({ORDER_HISTORY_ID}) but this just gives me overall count figure.

Any help would be gratefully received.

Thanks


UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[IanWaterman]

Probably only do this with a formula for each hour

@8-9am
If Hour(Datetimefield) = 8 then 1 else 0

Repeat for each working hour.

Then in report footer

@AveOrder/min 8-9am

Sum(@8-9am)/60

Repeating for each hour range

Ian

 

[CR85user]

Couldn't you just use something like:

COUNT ({ORDER_HISTORY_ID},hour({datetimefield})) / 60

Just thinking out loud as I am not at my workstation at the minute.

What basis do you want to do the average on?

To physically add a 1 for each line you can simply add a formula:

//Formula to display 1
1

Stick that on the line and it would give you that physical count although tbh it is not needed as there are better & cleaner ways to perform the count.

If you can confirm for example; what kind of data you would expect to see and how you wish to group it then hopefully we can put an answer together for you.

I think Ian has a viable approach on limiting it by the average for the hour.

'J

 

[urbanhim]

Thanks for your help guys, reall appreciate your input...

I currently have a report with the following information:

TIMESTAMP (selection criteria to currentdate-1)
ORDER_HISTORY_ID

I then need a line graph which will show hours along the bottom, and amount up the left, with two lines in it...

One showing total orders per minute
One showing average orders per minute

Hope this makes sense, the idea seems really simple, and my management are adament that as their report writer i get this to them today!! ;-)

Thanks peeps


UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

I was thinking that if i added a count field which worked like this:

TIMESTAMP ORDER_HISTORY_ID @COUNT
10:04:31 45678 1
10:04:33 45679 2
10:06:12 45680 3

I have a Sum field which shows total of Orders per minute, this is simply doing a count of ORDER_HISTORY_ID i was thinking that then with my @Count field (which i cant workout how to do) then I could have an Average sum on the Count field, if that makes sense!?

Cheers












UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[IanWaterman]

Create a formula and group on that
@minute
minute(Time stamp)

Create summary on that group
count(id, @minute)

Hopefully you can chart on that, against another formula
@hour
hour(Time stamp)

Average is relative, ie average over what period an hour a day?
if an hour

@avg/min
count(id, @hour)/60


Ian

 

[urbanhim]

Yes sorry, I was hoping that I could show an average along multiple timescales in various charts. I know from the chart expert I can pick whether to show Hour, Minute, AM/PM etc, I wanted to do that on multiple charts showing average orders (ie, average per hour, average per minute, average per AM/PM etc) along with Totals too (preferably in same chart).

I will try your suggestions, and I am very grateful for your responses

UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

Ive gone with this approach...

Ive created a formula for Hour:

@Hour
hour({ORDER_HISTORY.MODIFIED_DATE})

And then grouped on that field. I have then created my average formula:

@Average
Count ({ORDER_HISTORY.ORDER_HISTORY_ID},{@Hour})/60

And dropped that into the group line. I can now add this into a chart and it appears to be working a treat!

Thanks so much for this, really helpful!

UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]