Jump to content

How to get weekly average


This topic is 5687 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5687 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.