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: ...
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
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago
    Will try this. Thank you for the help!