Thread: What is the formula to create a monthly and yearly report?

1. What is the formula to create a monthly and yearly report?

Greetings,

I am new to the Alpha Five 9 Platinum series and I am hoping to find an answer to my problem. What is the formula to create a monthly and yearly report? I would like to be able to tally up the sales for each month and the grand total for the year. This is what I am stuck on. I thank you for your help as I look forward to your response.

Regards,

Pat

2. Re: What is the formula to create a monthly and yearly report?

Hi Pat,

Welcome to Alpha,

When is a year? And when is a month? By name or a number of days? I know this sounds like daft questions but you have know where the start and end points are to use in the filter for your report.

Are you inputting start and end dates when you run the report or is the report getting the values by some other means. Maybe you just want to run the report by selecting just the year. Would that be the current year or a year the user selects?

Sorry to throw this back at you but as you can see from my few questions your question cannot be answered with a formula with out more info.

3. Re: What is the formula to create a monthly and yearly report?

Originally Posted by Patd
What is the formula to create a monthly and yearly report? I would like to be able to tally up the sales for each month and the grand total for the year.
It's not so much the formula (although that too). Put a group break in your report. Select "expression" and put in month(your_date_field_here). Check the box for a group footer. Drag a new calculated expression from the task bar DragnDrop List and create a group total (that will be your monthly total). If your report will be for more than one year, duplicate the above for grouping by year (expression = year(your_date_field_here) and then a similar calc total for that group. If you report will only be for one year a t a time you will just need a grand total calc. In the latter case, you will need to filter upon the year before running the report.

4. Re: What is the formula to create a monthly and yearly report?

Originally Posted by Keith Hubert
Hi Pat,

Welcome to Alpha,

When is a year? And when is a month? By name or a number of days? I know this sounds like daft questions but you have know where the start and end points are to use in the filter for your report.

Are you inputting start and end dates when you run the report or is the report getting the values by some other means. Maybe you just want to run the report by selecting just the year. Would that be the current year or a year the user selects?

Sorry to throw this back at you but as you can see from my few questions your question cannot be answered with a formula with out more info.
Hi Keith,

I thank you for your response. The year is 2009 and the month is by name. I am inputting start and end dates and it would be the current year. Again, thank you.

Regards,

Pat

5. Re: What is the formula to create a monthly and yearly report?

Originally Posted by Peter.Greulich
It's not so much the formula (although that too). Put a group break in your report. Select "expression" and put in month(your_date_field_here). Check the box for a group footer. Drag a new calculated expression from the task bar DragnDrop List and create a group total (that will be your monthly total). If your report will be for more than one year, duplicate the above for grouping by year (expression = year(your_date_field_here) and then a similar calc total for that group. If you report will only be for one year a t a time you will just need a grand total calc. In the latter case, you will need to filter upon the year before running the report.

Greetings Peter,

Thank you so much for your help. I was trying to find the formula in the Alpha Five Made Easy book and the Functions Handbook. Maybe I missed it in the functions book? Again, thank you.

I hope you don't mind me asking this other question on a different project?

I am trying to keep track of dog license tag numbers for the current year, but when next year comes around, I want to be able to carry over those dog license tag numbers to next year while I would be able to have a seperate field to insert the new dog license tag numbers. If this is possible, what would I need to do? Thank you again Peter and enjoy your week.

Regards,

Pat

6. Re: What is the formula to create a monthly and yearly report?

I am trying to keep track of dog license tag numbers for the current year, but when next year comes around, I want to be able to carry over those dog license tag numbers to next year while I would be able to have a seperate field to insert the new dog license tag numbers. If this is possible, what would I need to do?
You need to change the structure of your database.

Think of an invoicing program that had only one field for product sold. You don't add more fields to handle more items. You have parent table that stores the invoice "header" information linked to a child table that stores individual detail lines. This allows for multiple items per invoiee and multiple invoices per customer.

In your situation you need much the same sort of setup but your child table would record the tag nmber and the date issued as well.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•