Discussions

Back to discussions
Expand all | Collapse all

4 weekly inputs = 1 monthly amount

  • 1.  4 weekly inputs = 1 monthly amount

    Posted 02-13-2020 14:45
    I have weekly inputs on a table for 8 different fields. I need to be able to sum each field each month and use that in other formulas. This will be ongoing  so I need the summation to be by month & year 
    example
    Date Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7 Field 8 Machine
    11/1/2019 15.73 228.43 145.8 0 0   0 3396 Machine 1
    11/7/2019 0 21.45 4.48 0 0   0 2214 Machine 1
    11/13/2019 0 27.22 0 0 0   0 4452 Machine 1
    11/19/2019 0 179.18 163.13 0 0   0 4572 Machine 1
    November 15.73 456.28 313.41 0 0 0 0 14634  
    11/1/2019 15.83 293.32 147.86 0 0   0 3396 Machine 2
    11/7/2019 0 29.95 23.75 0 0   0 2214 Machine 2
    11/13/2019 0 45.26 32.95 0 0   0 4452 Machine 2
    11/19/2019 0 227.35 177.86 0 0   0 4572 Machine 2
    November 15.83 595.88 382.42 0 0 0 0 14634  
    11/1/2019 30.13 235.97 0 0 0   0 3396 Machine 3
    11/7/2019 0 23.13 0 0 0   0 2214 Machine 3
    11/13/2019 0 32.6 0 0 0   0 4452 Machine 3
    11/19/2019 0 191.66 0 0 0   0 4572 Machine 3
    November 30.13 483.36 0 0 0 0 0 14634  

    This is a simplified version of what i need but there will be 52 inputs for each field: 8 fields/ month
    all of this will be different data for 1000+ machines.

    ------------------------------
    Rhonda Parriet
    ------------------------------


  • 2.  RE: 4 weekly inputs = 1 monthly amount

    Posted 02-13-2020 15:57
    Rhonda,

    The simplest way to summarize in Quick Base is from a child table to a parent table.   How is the data above related to other business objects?

    Here is one potential architecture


    Now you can create a Summary Field in the Machine Table 

    Here is an example where we are going to count the number of vibration incidents



    You build out the rest of the information you need the same way for the other data fields in your Machine Inspection table.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

    Original Message


  • 3.  RE: 4 weekly inputs = 1 monthly amount

    Posted 02-14-2020 07:46
    The fields need to be summed based on the month.  Each of these "summed" fields are used in calculations to create a stacked bar chart showing values month over month for a specific machine.
    I have one table with machines
    ---fields are department, machine
    one table with the fields that are calculated by month. 
    Fields include machine, month, year, and the several formula fields
    ex total breakdown/minutes ran
    total quality / minutes ran
    stop/minutes ran
    The three above fields added together to make another field
    In total there are 24 formula fields based on the input fields.  
    A third table will have these weekly inputs.
    These fields are date (week-ending), machine, and the input fields
    Breakdown, stoppage, quality, setup, tool, minutes ran

    ------------------------------
    Rhonda Parriet
    ------------------------------

    Original Message


  • 4.  RE: 4 weekly inputs = 1 monthly amount

    Posted 02-14-2020 11:11
    Rhonda,

    So sticking with the proposed architecture, you will need to make separate reports based upon what period of time you want the data summarized over.   I am also assuming you are going to do a report for each machine to keep it readable.

    Some sample data


    Here is Chart Detail



    And then the result for the Press




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------

    Original Message


  • 5.  RE: 4 weekly inputs = 1 monthly amount

    Posted 02-14-2020 13:01
    Edited by Adam Keever 02-14-2020 13:02
    Add a formula rich text field and create a formula to combine the date and machine like this:
    ToText(Year([Date]))&ToText(Month([Date]))&" - "&[Machine]​


    Then you can graph on the combination of the year/month and machine (these are unique):

    Here is what the graph looks like with some additional data added:

    You can filter by machine:

    You can also set a report filter to <ask user> for the machine or add the date to the dynamic filters list and select specific time periods. Month has to handled this way since charts order either ascending or descending so using the text for the month, such as November, would disorder the data in a chart since you cannot manually select the order. Also as you add data year over year, the data for multiple years of November would be charted in the same bar.




    ------------------------------
    Adam Keever
    ------------------------------

    Original Message