Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

Summing up a numeric field from different entries.

Hi, I would like to ask how to solve the example below:

Under the Salary table, I have three records: Record 1, Record 2 and Record 3.

Record 1:
  Month: 201902
  Money: 80

Record 2:
  Month: 201911 
  Money: 90

Record 3:
  Month: 201905
  Money: 70
 
 Is there a way to combine the Money field of the 3 records in another field (i.e Year 2019)? So for this example, the Year 2019 field should get the sum of 240.

 Thank you for the help!
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    no problem,
    Just make a new table called Years and load it with a bunch of years.
    2019
    2020
    2021  etc

    then make a formula numeric field on your Records table

    ToNumber(Left([Month],4))

    Then use that field in a relationship to Years and sum up your records.


    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      7 years ago
      Hello!

      Can I also use s similar approach if the Fiscal Year is something like this:
      FY17/18 - 201703 up to 201802
      FY18/19 - 201803 up to 201902

      So, the field FY17/18 can sum up the total Money field data for records that contain the Month field containing 201703 or 201704 or...201802?

      Thank you!

    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      I think that this will work
      var number YYYYraw = ToNumber(Left([Month],4));
      var number MM = ToNumber(Right([Month],2));

      var number YYYY = If($MM<=2, $YYYYraw - 1, $YYYYraw);
      var number NextYYYY = $YYYY+1;

      "FY" & Right(ToText($YYYY),2) & "/" & Right(ToText($NextYYYY),2)

      The entries in your years table will need to be in that that FY format as well like FY17/18