Forum Discussion

KevinGullickson's avatar
KevinGullickson
Qrew Trainee
5 years ago

Year-Month Reference Field

I have a table called Orders.  Fields include Creation Date (YYYY-MM-DD), Total Revenue, and Total Refunded for each customer's order.  I need to create a new table to sum the Total Revenue and Total Refunded by Year-Month Combo.  I'm doing this so I can figure out the Average Refunds per Month (which I apparently can't do using a Summary Table on the Orders table).

I'm stuck trying to figure out how to create a field on the Orders table that is just the Year-Month Combo (date field?  text field?) that I can then use as the reference field on the parent Year-Month Table (can I use a formula field as a reference field?).  

Thanks for any clarity you can provide.

------------------------------
Kevin BP
------------------------------
Formulas and functions
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Can you tell us more about that field [YYYY-MM-DD] what field type is it and it it is a formula field what is the formula.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • KevinGullickson's avatar
      KevinGullickson
      Qrew Trainee
      5 years ago
      "Creation Date (DD-MM-YYYY)" field is the date that the Customer placed the order and is imported into the database as a standard Date field.  Not a formula field.

      ------------------------------
      Kevin BP
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        I Suggest at you use excel to create at least 10 years (120 records) with a text field in the format YYYY-MM

        For Example

        2021-03

        Import that into a table and set that field as the key field.

        Then on the details side, make the formula 

        List("-", totext(year([Creation Date (DD-MM-YYYY)])),
          padleft(totext(Month([Creation Date (DD-MM-YYYY)])),2,"0"))


        Make a Relationship back to the YYYY-MM Months table.

        post back if that formula does not work due to syntax errors.

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