Jump to content

  •  

UPGRADE DEADLINE - SEPTEMBER 26, 2014!
FileMaker Inc. has a deadline for users of version 10,11, 12 as Individual box or volume licenses (with expired maintenance).
If you don't renew your maintenance and upgrade to FMP 13 you will no longer be eligible to upgrade, at the discount pricing.

Volume Licensing upgrade pricing for FileMaker Pro 13, FileMaker Pro 13 Advanced and FileMaker Server 13 will be discontinued.
Individual upgrade pricing for FileMaker Pro 13 and FileMaker Pro 13 Advanced will increase after September 26, 2014.
As of 27-September-2014, FileMaker 10 products will no longer be available for purchase or support.

http://help.filemaker.com/app/answers/detail/a_id/13865


Photo

Run-rates in sub-summaries


  • Please log in to reply
7 replies to this topic

#1 brainonastick  journeyman

brainonastick
  • Members
  • 156 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 6h 42m 15s

Posted 29 January 2014 - 06:08 PM

Hi there,

 

I have a report which generates course run rates for a particular date period which works fine.  The report includes a run rate which is simply the total number of course days that ran (were not cancelled) divided by the total number of days in the report period. All well and good.

 

I need to add some sub-summaries so that the run rates are also calculated when the courses are sorted by course stream (streams are classifications of training like subject areas). 

 

So a given report for a month would have up to 13 sub-summaries based on course stream each with its own run rates per course stream displayed.

 

However I can't work out how to get run rates to appear - if i try to divide the summary of the days that ran by the summary of the total days, in the sub-summary field, I just get the run rate for the whole group of records.

 

For example:

 

Course 1 stream 1 - duration 2 days - not cancelled

Course 2 stream 1 - duration 3 days  - not cancelled

 

Course 3 stream 2 - duration 1 day - cancelled

Course 4 stream 2 - duration 2 days - not cancelled

Course 5 stream 2 - duration 1 day - not cancelled

 

Course 6 stream 3 - duration 4 days - not cancelled

Course 7 stream 3 - duration 2 days - cancelled

 

the sub-summary for stream 1 should show a run rate of 100% (5/5 days ran)

the sub-summary for stream 2 should show a run rate of 75% (3/4 days ran)

the sub-sumamry for stream 3 should show a run rate of 66% (4/6 days ran)

 

I can run off these reports by searching by one stream at a time but i want a report that sub0summaries run rates for multiple streams over a given period.

 

Any advice gratefully received.


  • 0

#2 comment  consultant

comment
  • Members
  • 24,118 posts
  • Time Online: 326d 6h 12m 30s

Posted 29 January 2014 - 06:15 PM

What does a record in this table look like?


  • 0

#3 brainonastick  journeyman

brainonastick
  • Members
  • 156 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 6h 42m 15s

Posted 29 January 2014 - 06:40 PM

What does a record in this table look like?

i don't understand the question I'm sorry - its a report with records in list view with a sub-summary above and below the body and a trailing grand summary - is that what u mean?


  • 0

#4 comment  consultant

comment
  • Members
  • 24,118 posts
  • Time Online: 326d 6h 12m 30s

Posted 29 January 2014 - 06:46 PM

No, I mean what fields does it have?


  • 0

#5 brainonastick  journeyman

brainonastick
  • Members
  • 156 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 6h 42m 15s

Posted 29 January 2014 - 07:03 PM

No, I mean what fields does it have?

BODY

 

course_code (text)

course_name (text)

dates_summary (text)

venue_suburb (text)

course_length (number)

cancelled_flag (text, checkbox = x)

rescheduled_flag (text, checkbox = x)

count_courses = count(course_code)

day_cancelled =  if(cancelled_flag = X, course_length, 0)

course_ran = if(cancelled_flag = X, 0, 1)

days_ran = if(cancelled_flag = X, 0, course_length)

total_registrations (related field from registrations table)

 

TRAILING GRAND SUMMARY

 

total_courses = summary of count_courses

total_courses_ran = summary of course_ran

total_days_ran = summary of days_ran

total_days_cancelled = summary of days_cancelled

run_rate_courses = total_courses_ran/total_courses

run_rate_course_days = total_days_ran/total_days


  • 0

#6 comment  consultant

comment
  • Members
  • 24,118 posts
  • Time Online: 326d 6h 12m 30s

Posted 29 January 2014 - 07:21 PM

I am sorry, but I am still missing the fields that would tell us in what month and which "stream" the course should be reported. I'll skip the month issue and assume the following fields:

• course_length (number)
• cancelled_flag (text, checkbox = x)
• days_ran = if(cancelled_flag = X, 0, course_length)

stream (text or number)

total_days = summary, total of course_length
• total_days_ran = summary, total of days_ran


Now you need to add a calculation field (result is Number) =

GetSummary ( total_days_ran ; stream ) / GetSummary ( total_days ; stream )

and place it in the sub-summary (when sorted by stream) part.

 

 

 

---

P.S.

Unrelated to the current issue, but your cancelled_flag field should be Number, using a checkbox with a value list of "1". Then days_ran can be simply =

If ( not cancelled_flag ; course_length )

  • 0

#7 brainonastick  journeyman

brainonastick
  • Members
  • 156 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 6h 42m 15s

Posted 29 January 2014 - 07:56 PM

sorry there is a stream field for each record and course_date field which is used to do a search from 1st to last of the month usually - thanks for the above though its really helpful


  • 0

#8 brainonastick  journeyman

brainonastick
  • Members
  • 156 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 6h 42m 15s

Posted 30 January 2014 - 02:35 PM

thanks so much for this - huge help!


  • 0




FMForum Advertisers