Forum Discussion

JordanBeatty1's avatar
JordanBeatty1
Qrew Captain
5 years ago

Best Way To Summarize Numbers To Calculate Overages

The table structure is one Parent Company has many Companies and one Company has many activities. The parent company record contains a limit to calculate overages. The activity records contain a Time Logged Field and a Date/Time Field.

Currently, I summarize Time Logged from Activities into Companies, filtered to only summarize from previous month. That data is then summarized into the Parent Company where that total - limit is the overage for the previous month. This works great for calculating overages for the previous month. But what if I want to calculate the previous quarter, or a random month from the previous year?

Should I be trying to summarize this in the Activity table or Parent Company Table? 

I can easily sort my Month and Date in the Activity table, but can I pull down the limit and use it in a calculation against a total in a summary report?



------------------------------
Jordan Beatty
------------------------------
Relationships

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    5 years ago
    I don't think there is a way to use the Limit down at the Activity level in overage calculations. 

    If you want to have adjustable time periods, you can set up a new table called Date Selection, with a single record. Set up two fields for start and end dates. It will be the only record so Record ID =1.

    link that in a relationship to all activities with a reference field which is a formula that calculates to 1.

    That will allow you to create summary fields where the dates fall within the Date Selections. 

    I suggest that you show the overage report an a Report Link right on the Date Selector Record, so it's obvious what the selected dates are.  Or else also link it to the Parent Company and/or Companies to b3 able to show the date range on the reports. 



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • JordanBeatty1's avatar
      JordanBeatty1
      Qrew Captain
      5 years ago

      My mistake, didn't realized responding through email sent a direct message.

      So it's been a reallll long time since I have had to do anything Quickbase related. I have it linked so that a table called Date Range has many activities. So I changed the reference field from numeric to numeric formula, but it keeps telling me it is expecting text?



      ------------------------------
      Jordan Beatty
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        5 years ago
        The formula for the reference field in Activities should just be

        1

        ie, just a 1


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