Filtering A Monthly Summary

Support, Questions and Suggestions for the FM7 Version of CC Calendar.
Posts: 18
Joined: Thu Apr 09, 2009 8:32 am
Location: Missouri
PostPosted: Fri Apr 10, 2009 7:33 am
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!
Robin
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sun Apr 12, 2009 5:51 am
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
John Sindelar
SeedCode
Posts: 18
Joined: Thu Apr 09, 2009 8:32 am
Location: Missouri
PostPosted: Mon Apr 13, 2009 6:17 am
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?
Robin
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Mon Apr 13, 2009 6:22 am
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 ) )
John Sindelar
SeedCode

Return to CC Calendar (FM7/8)

Who is online

Users browsing this forum: No registered users and 3 guests

cron
(855) SEEDCODE
[email protected]
Follow us: