How to get weekly average

[Saj0916]

Hello,

I am new here and was wondering if anybody could help me out here. I have an account id, date, transaction id, transaction amount(and total amount). I was wondering how I could sort and make a field definition for the amount spent each week and month by an individual person. Thanks for the help.

[John Mark Osborne]

You need a subsummary report. Create a new layout with a subsummary part sorted by the account id field (which I am guessing identifies a person). Add the account id and any other fields you want to the subsummary part. Also, create a summary field that totals your amount field and place it in the subsummary part. You don't need any other parts unless you want them.

When you want to view the report, find the records you want to report on (maybe a date range find) and then sort by the account id field. You can't view this report in browse mode so enter preview or print.

[Saj0916]

Thanks for the quick response. If I have transactions from multiple months; Is there anyway I can I create an average monthly transaction and average weekly trasaction field definition. I wanted to have both those fields printed in the subsummary for each user. Thanks.

Edited May 10, 2006 by Guest

[John Mark Osborne]

You can have three subsummary parts in a single report. The same summary field will average differently depending on the context. So, you can create a subsummary by Account ID, Month and another by Week. There is a function called WeekOfYear which will tell you which wek a date falls into:

WeekOfYear(DateField)

There is one for Month:

Month(DateField)

Base your subsummary parts on these calculations. Again, you can place them all in a single report layout or you can use them on separate reports. Play around with the variety of combinations till you get the report you desire.

[comment]

This will work only if the records are all in the same calendar year (as I have already pointed out here) - or at least sorted by Year, before being sorted by Month/Week.

Using WeekOfYear() is even more problematic: unless January 1 falls on a Sunday, the week containing January 1 will be split into two fractions, both counting as a full week. This may not be the desired outcome.

[John Mark Osborne]

Just concatenate the year to the month or week to make the calculation work across multiple years.

[comment]

Yes, you could do that. But then the result will be text. So you will also need to add a leading zero to all months/weeks that are less than 10 - or it won't sort properly.

And you'll still have the problem with the last/first week of the year.

And, if you want a nice heading for the subsummary part like "May, 20006", you will need yet another calculation field just for this purpose.

While all this can be done by a single, simple calculation with a date result.

[Saj0916]

Thanks for all the help, I'm still trying to figure it out.