Need to create a simple Average per month formula 2

[urbanhim]

Everytime one of our clients reads a news or info article on our website, an entry is added against their account ID for that date for either "Info" or "News". So I have a table with just three fields in it:

Date
Type (either "News" or "Info"
Account_ID

I have two groups, group1 on Type and group2 on date.
I have created a distinctcount formula:

@DCount
distinctcount({AUDIT_HEADLINES.ACCOUNT_ID},{AUDIT_HEADLINES.ACCESS_TIME})

So for each day in the month I have...

Date Type @DCount
01-Sep News 1245
02-Sep News 2345
03-Sep News 2134

01-Sep Info 3400
02-Sep Info 2900
03-Sep Info 3124

What I want now though is an average at the bottom of each group, showing average per day for the month. So it would look like this:

Date Type @DCount
01-Sep News 1245
02-Sep News 2345
03-Sep News 2134
Avg 1950

01-Sep Info 3400
02-Sep Info 2900
03-Sep Info 3124
Avg 3110

I thought that I could do a formula like this:
average{@count} but it wont let me.

At the moment we are exporting this data to Excel, then at the bottom of the table, simply putting a formula in which is =average(b1:b32) etc, but we want to be able to do this in the report itself to save manual intevention.

I hope this makes sense, and grateful for any help!?

Thank you

UrbanHim
Crystal XI Report Writer
London

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

 

[Charliy]

Are you wanting to divide the count by number of days in the month?

DistinctCount({table.date},{whatyourgroupedon}) will give you the number of days if your operation is 7 days a week.

 

[lbass]

Use formulas like this:

//{@accum} to be placed in the group #2 header or footer and suppressed:
whileprintingrecords;
numbervar sumdcnt := sumdcnt + {@DCount};
numbervar cnt := cnt + 1;

//{@reset} to be placed in the group #1 header and suppressed:
whileprintingrecords;
numbervar sumdcnt;
numbervar cnt;
if not inrepeatedgroupheader then (
sumdcnt := 0;
cnt := 0
);

//{@ave} to be placed in the type group #1 footer:
whileprintingrecords;
numbervar sumdcnt;
numbervar cnt;
sumdcnt/cnt

-LB

 

[urbanhim]

Brilliant... I dont know what its doing, or how it works but it does and im very grateful!

Is there anyway I can get total average per day in the same report regardless of view type.

I can do this in a seperate report, by deleting the Type Group 1, and just having a Group on date, and then putting the Avg formula in report footer, this works fine. But cant seem to do it in the same report.

So it would look like this:

Date Type @DCount
01-Sep News 1245
02-Sep News 2345
03-Sep News 2134
Avg 1950

01-Sep Info 3400
02-Sep Info 2900
03-Sep Info 3124
Avg 3110

Total Avg 4232

Many thanks


UrbanHim
Crystal XI Report Writer
London

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

 

[lbass]

Please explain how you arrived at the average of 4232.

-LB

 

[urbanhim]

The average of 4232 isnt the actual figure, I just typed that in, but how I would like to get to that figure is by simply calculating the average per day for each unique client, regardless of whether its a Info or News view.

To clarify, the Total Unique is not a sum of the two averages, because if a client in one day views an Info and a News item, it should only count them once for a unique count, and the average per day for month should be based on this.

For instance, I would therefore only be using two fields in my report:

Date and Distinct Count of Account ID's.

I have managed to do this in a seperate report by having just a group on date for each day, and then distinct count of Account_ID, then your Ave formula I have dropped into the footer, works fine. But we want this in the same report so we will effectively have three average figures!

Hope this makes sense!! Thanks



UrbanHim
Crystal XI Report Writer
London

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

 

[lbass]

I think you should just do this calculation in a subreport--using the separate report that gave you the correct calculation.

-LB

 

[urbanhim]

Mmm... Not a bad idea at all! Thanks

UrbanHim
Crystal XI Report Writer
London

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