Forum Discussion

DavidBerndt1's avatar
DavidBerndt1
Qrew Member
5 years ago

Forecast vs actual by months

We have a forecast table that users update their forecast of total visits on a monthly entry basis for each project (i.e. Proj 1, Jan 2021 = 10, Proj 1, Feb 2021 = 15,  Proj 2, Jan 2021 = 20, etc).  There is a visits table (not currently related to the forecast) that has an entry for each individual visit that includes that date.  

what I am trying to figure out is how to summarize all the visits from that table by project and month into a report or field in the forecasting table so we can provide a report that shows forecast vs actual.  Thoughts?



------------------------------
David Berndt
------------------------------
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Can you tell us the field names and field types for the forecast table?
     e.g. what does a record look like?  and is the Project Name field a lookup of [Related Project]?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • DavidBerndt1's avatar
      DavidBerndt1
      Qrew Member
      5 years ago
      The forecast is the project name which is a lookup to the project table, a "start date" for each month which is the first of the month, numeric field for the forecast.  

      The visit table has the same project name (via an intermediary table), the actual date, and other necessary fields

      ------------------------------
      David Berndt
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        You will need to set the Key field to be the result of a formula which concatenates the Related Project and the Date.  There would need to be a formula calculation and then a form rule to populate the new key field with the value of he formula or else some kind of Automaton to auto create forcasts records with the correct Key.

        Then the child table will be able to calculate the same Key by formula so it will Link up to he correct Forecast.  But in your response you said that there was not a direct relationships between Visits and Projects.  The problem will be that a lookup field may not be used in a formula for the reference field on he right side of a relationship if it involves a lookup field.

        The only work around to THAT is to have an automation backup up I suggest by a an overnight saved table to table import running via an Automation to copy over the lookup field into the scaler (data input) field.

        So the formula would be like 

        List("-" ToText([Related Project Maintained by Automation], ToText([visit date])))


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------