8 Replies Latest reply on Nov 28, 2013 11:21 PM by ultranix

# Average of results of last 30 days

### Title

Average of results of last 30 days

### Post

Hey,

I want to be able to track the "running" average of sales calls made each day using data of last 30 working days. There's a calculation field, day_call_tot, which calculates all the sales calls made during the day. I want not to only be able to track average of calls made during last 30 working days from today, but from any day selected (date would be entered in global date field - xdate).

My first thought of having this solved is:

1) to make to add another date calculation field, in which, if difference between record date and xdate (global date field) is no higher than 42-44 days (i.e. 30 working days), then it would return the result of xdate, otherwise - blank.

2) add another occurence of table with relationship: date = xdate.

3) and then add a summary field with average option (xdate).

But this approach is still rough and needs testing and improvements. So question #1 is: maybe there's an easier way?

Question#2 is: how to calculate difference between calls made today and previous working day (keeping in mind, that if you are counting it on monday, it would have to take the friday's record, not sunday's, because it's not a working day). So, it should basically have to pick the record of closest date, if it's not higher than 5 (no holidays are longer than 2 days + 2 days of the weekend).

• ###### 1. Re: Average of results of last 30 days

I get the idea of last 30 days and you can set up a relationship that matches only to records from Get ( CurrentDate ) to Get ( CurrentDate ) - 30, but if you specify a date, does that mean that you want those records from Get ( CurrentDate ) back to xdate?

I think your whole process of counting days and referring to the previous day looks more complex than just skipping weekends--something that a calculation could easily handle, but must also account for holidays--which can't be handled as easily in a calculation.

A calc for just skipping the weekend: cPrevDate =

If ( DayOfWeek ( DateField ) = 2 ; Datefield - 3 ; DateField - 1 )

• ###### 2. Re: Average of results of last 30 days

Question #1: As for last 30 days. If xdate is today, i.e. 2013-11-25 - i want it to display records from last 30 working days.

If xdate is 2011-06-01, it should display records from last 30 working, starting from that day.

Question #2: What if we don't stick to days of the week and just calculate such thing:

if no record for previous 5 weekdays (date field is named date) then the field is blank. if a previous record is 5 days ago, then calculate the difference using it, else if a previous record is 4 days ago, then calculate difference using it (keeping in mind that there's no record 3,2 or 1 day ago).

I believe it's not rocket science calculation :)

• ###### 3. Re: Average of results of last 30 days

Is it possible?

• ###### 4. Re: Average of results of last 30 days

Sorry, but I was out of town transporting my wife to a medical appointment at Stanford.

Q1.

You can use Set Field [Yourtable::DateField ; YourTable::xDate - 30 & "..." YourTable::xDate ] to find a found set of records so that a summary field can compute an average. A Sub Summary layout part can compute daily averages in that range if you sort your records by date and specify that field as the break field for the sub summary layout part.

ExecuteSQL, in FileMaker 12, can also produce such sub totals using a WHERE clause that limits the records queried to just those in a date range using inequality operators.

It could also be done with a portal, but such an approach is much more complex and requires a single record for each date in a related record--but note the option that this opens for you for both Q1 and Q2:

If you set up a table where you have one record for each day that is not a weekend or vacation day, then you can use a relationship between this table and the table of data from which you want daily averages to get an average for each day. GetNthRecord can extract the day that is the 30th related record and thus 30 days if you use a relationship that matches only to records that are < to xDate.

• ###### 5. Re: Average of results of last 30 days

As for question2, I already succeeded to implement what i want adding self-joined table, with relationship TABLE::date > TABLE::date. It worked greatly.

As for Question 1, which seemed way easier at first place, I'm still messed up. I tried to add another calculation field (call_tot_disp) with expression:

Case (xday - day > 92; ''; day_call_tot) // I used this to display records for last 3 months, xday and day on the current record are the same, so, let's say, if xday is 2013-11-12, then the calculation field display day_call_tot field data up to 2013-08-13 and for dates earlier than that it would be blank.

Then I used calculation field (avg_day_call) to compute averages (=Average(TABLE::call_tot_disp). Summary field didn't work, as I needed averages for particular type of calls (incoming, outgoing, colleague), so i made self-joined table and i used calculation field with average of those table's record.

It kinda works, but i need to accomplish a thing listed in step 2

Step#2. I also want to have another field, where data would be placed in indexed number field (let's name it day_call_num) as it is used in other calculations and blanks (in case dates are way too far from that record in a calculation field) are not acceptible. (loop/set field script here only comes to my mind, but i have 10.000 records and each day i will get another one). Could you please give me script steps? (as i'm not experienced with loop scripts and usually fail to write exit loop line, so it runs forever until i hard-close it)

Step#3. As for new records - maybe there's a way to construct a scripted find script to find blank records in day_call_num, and only loop/set field if isempty condition is met? I thought about OnRecordLoad script trigger option, but it is possible that not all records are loaded, and still all records need to have that field set and not blank as it is integral part of whole calculation.

Another issue - if we do a scripted find and find only records with empty day_call_num, would summary field with averages will return appropriate results, as not all of the records would be displayed?

How could this be achieved?

• ###### 6. Re: Average of results of last 30 days

I don't see the need for the calculation field that you have here. I'd match records by relationship or scripted find to get the needed set of records for the needed totals and sub totals. Execute SQL also comes to mind as a way to get one or more sub totals from the records in a table.

There are two ways to set up a "date range" relationship:

Table1::StartDate < Table2::Date AND
Table1::EndDate > Table2::Date

or

Table1::DateList = Table2::Date

DateList would be either a text field or a calculation field that returns text. The values in this table would be a return separated list of dates from StartDate to EndDate. This is often produced using a custom function that recurses to produce the list of dates.

• ###### 7. Re: Average of results of last 30 days

I am confused. I use Filemaker 11, and i'm not familiar with Execute SQL feature, sadly.

startdate and enddate for each record would be different, as, for example, if current_date is 2013-11-28, then start date would be the same, and end date would be -92 days from that day (2013-08-28), then, if current_date is 2013-11-27, end date would be 2013-08-27 (-92 days), etc.

As for DateList, would it be accomplished by setting up a calculation field with expression: List (date_field)?

Even if i go through this, how will i see my 3month averages? which table should the field be based?

• ###### 8. Re: Average of results of last 30 days

Nevermind, I woke up in the morning with the clear head and was able to implement your solution. Thanks again.