Hi, I'm new to FMP and I have a list of records that include:  Territory, CallDate and other record-specific detals (products, etc.).  I need to find out how many calls each territory averages in a given date range.  I have a calculation to yield the business (work) days for a range, which would then be used as a divisor for my calculation:  #Calls (= #territory-specific records)/number of WorkDays = Call average

 

While I have been able to get some sub-summary type fields in one layout (trailing summary with a resetting count for each territory)  I can't find how to include that territory-specific count in my calculation ... It keeps using the grand total only ... ?

 

My end game is to generate a chart that will show the call average for each territory ...  sorry if my explanation is cloudy, I'm learning the terminology too:)

 

Thanks,

Lance

I'm not positive I understand your need here, but it sounds like you want a calculation based off a Territory and CallDate relationship. (NB: This could be done without a new relationship, via ExecuteSQL, but that's a little more advanced. Best to learn native FM relationships first).

 

So in your Territory table, you'd want to create two new global date fields, and call them something along the lines of StartDate and EndDate. In the Relationship Graph, you'd want to make a new table occurrence of your Calls table (let's call it CallsByTerritory for the moment), and relate it to your Territory table via Territory key, and the global date fields. For the date fields, you'd want to make StartDate <= CallDate, AND EndDate >= CallDate.

 

Once that is done, you will want to create an unstored calculation field in Territory, that basically reads Count(CallsByTerritory::primary_key)/(number of WorkDays calculated from the StartDate and EndDate globals)

 

After all that, all you have to do is set the date globals by script or manually, and you'll have your Call Average by Territory, for a given date range.

 

Hope that's what you needed!

 

K

You may want to have a look at the GetSummary function.

I'm dealing with a flat file database ... I would have to split it out to a new database in order to map relationships, which doesn't seem to be a 'best practice' (?).  It's frustrating because the data is all there.  I can sort and get sub-summaries that tell me the # of calls per territory (since it's sorted by territory) .... but 1)  I can't use the trailing summary and 2)  I can't find a calculation or script that will look at the range of dates, for the given territory, to calculate the the number of days ... it will only treat each record at a time ... not a range.

 

Thanks!

L.

Breaking up tables or normalizing is done all the time. I would suggest this IS best practice.

a. did you try my suggestion?

b. you can create relationships even with only one table. And if you do make new tables, you don't need a new database to do it. But where did you get the idea there's any best practice that says flat files are better?

 I can't find a calculation or script that will look at the range of dates, for the given territory, to calculate the the number of days

 

Well, it depends: if you want to count each and every day that falls withing the range, you can simply subtract the minimum date from the maximum. If, OTOH, you want to count only dates for which you have data, then it gets more complicated:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/?view=findpost&p=289204

 

In version 12, you can also use the ExecuteSQL() function to get a count of distinct values.

Sorry, I don't mean to imply flat files are better.  What I have learned from FMP is that relational files (ie. databases) are far more powerful, etc.  What I do find cumbersome is working out of specific layouts.  Since I'm coming over from excel, I have a greater comfort with the table layout views ... I am reviewing the get summary function and will do a table <=> table relationship ... 

Thanks!

If your intention is to summarize a found set, then using relationships may not be the best way to proceed.

I hate to ask this:  how do you use "get summary"?  I've tried it as a calculation field and I receive an error about it needing to be a summary type field.  If I try that I get nothing that permits me to enter in the getsummary() calculation ... ?  I've attached the field structure that I'm working with for a better visual.

Thanks

fmp pic for forum.tiff

Judging by the flurry of responses I would assume that the last question re:  GetSummary is ridiculously primary and not worth the time.  So I'll explain the difficulty I am experiencing in using the Getsummary function:  I have a Call_date field and a Call_ID field. The latter would be the unique identifier for each record in the table.  I am looking to enter this calculation:  GetSummary (Call_ID, Call_Date).  When I create a new field to house my GetSummary function, I must select 'summary' as it's type.  When I do this I am forced to choose one of Total, Average, running count, etc.  And FMP tells me which field will be selected (never the one's I need for my formula/calculation).  So, you see my difficulty?  The documentation doesn't elaborate well with respect to GetSummary .... I appreciate your thoughts.

Thanks!

The thing that's missing from your description is your goal. I am mostly guessing here, but try defining a summary field sCount as count of Call_ID. Then define a calculation field (result is Number) =

 

GetSummary ( sCount ; Territory )

 

This will return the sub-summary value of sCount when records are sorted by Territory. IIUC, you actually want to calculate =

 

GetSummary ( sCount ; Territory ) / x

 

where x is the number of days in the range?

When I create a new field to house my GetSummary function, I must select 'summary' as it's type.

No. You must select calculation as its type.