Hey All,
I am using the appointments as orders for my company. I am trying to figure out a way on the month layout to add a summary to add up all of the orders (appts) per month and i've added a field for the order total, so I would like a summary for all of the order totals per month.

The problem that i am running into is that it wants to calculate all of the 5 weeks that are shown on the weekly table. Say like in March, on week 5 only the first 2 days are were in March and the other 3 days are in April. How can i filter this so that the summaries only add up what is actually in the month I am viewing?

Any help would be amazing!
Thanks so much!

Hi. As I mentioned here, I think the simplest thing to do is to use a sub summary report for this.

But, if you want to see this total on the month view, you need to take into account the days-outside-of-month problem you've notices.

Seems like the best thing here would be to create two new calc fields in Filters one for the first day of the month in CalFilterDateGlob and a second for the last date. Then build a new relationship from CalendarMonthWeek to a new table occurrence of appointments using these two new fields:

FirstDayofMonthCalc <= ApptDateStart
LasttDayofMonthCalc >= ApptDateStart

Then you can create new calc field in CalendarMonthWeek to Sum() or Count () values from this month's appointments across this new relationship.

Hope that helps.

John

Hey John!
Thanks for all of the help.

Can you please help me with the calculations to figure out the frist day of the month and last day of the month?

After I have these calculations will my new sub summary change as I flip threw the months via the arrows?

mgxdigital wrote:After I have these calculations will my new sub summary change as I flip threw the months via the arrows?

It sure will. Here are some calcs you can use...

First day of selected month: Date ( Month ( CalFilterDateGlob ) ; 1 ; Year ( CalFilterDateGlob )

Last day of selected month Date ( Month ( CalFilterDateGlob ) + 1 ; 0 ; Year ( CalFilterDateGlob ) )