Average of results of last 30 days
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).
Thanks in advance.