2 Month or Quarter Rolling average

Question

Hi FolksI have been struggling with this issue. I have a series of invoices, recorded in an Invoice table. These are summarized by reporting quarter in a parent table. From this one is able to see the various types of spend as a percentage of total spend per quarter. The next step is that we need to look at the rolling average of current quarter and previous quarter as a percentage of a target. The quarters are linked to defined dates. The requirement is as follows:Average spend for Q1 plus average spend percentage for Q2. Then the average spend percentage for Q2 and  Q3. This continues over 80 quarters. Does anyone have any pointers on how to do this?Thank you------------------------------Matthew Kearns------------------------------

MarkShnierYou · Answer

Here is one solution.Make a relationship between Quarters to Quarters (ie related the table to itself).  Let it create a field for related quarter and then rename that to be call Related Previous Quarter.  If the Key field if Quarters is [Record ID#] then find a field on the Quarters record look it up and ands set edit [Related Previous Quarter] to set that as the Proxy field.Then make that a required field on the Quarters table and hand edit the 80 records to relate each Quarter to the Previous Quarter.  Then lookup anything that you need like total invoice value and make your formulas.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Sign up

Login to the community