Discussions

Expand all | Collapse all

Best Way To Summarize Numbers To Calculate Overages

  • 1.  Best Way To Summarize Numbers To Calculate Overages

    Posted 10-10-2019 19:27

    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
    ------------------------------


  • 2.  RE: Best Way To Summarize Numbers To Calculate Overages

    Posted 10-10-2019 22:46
    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
    ------------------------------



  • 3.  RE: Best Way To Summarize Numbers To Calculate Overages

    Posted 20 days 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
    ------------------------------



  • 4.  RE: Best Way To Summarize Numbers To Calculate Overages

    Posted 20 days 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
    ------------------------------