Forum Discussion

DennisAgle's avatar
DennisAgle
Qrew Trainee
8 years ago

How to show a variance in excess of X% between records

I have a table that shows the dollar amount of monthly transactions for each account. Every month we upload the new monthly totals for all the accounts. I'd like to be able to identify those rows where the dollar amount for a given month is in excess of 120% of the dollar amount for the same month the previous year for each account. I can't seem to wrap my mind around how to do that. What am I missing?
My fields include: [Account #] [Month] [Year] [End of Month for the month in question, basically a normal MM/DD/YYYY date field] [$ Amount]
Any suggestions?
Thanks in advance.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    in order to compare records in Quick Base, you will need to change the Key field of your data form the default [Record ID#] field to to be something which is  also Unique, but which will let you make a relationship so that the relationship knows which other record you want to compare to.

    If the data is being imported by Excel where you can create a formula text field, then my suggestion is to create a new field in the format Acct#-YYYY-MM.

    You can either re-import your current data or make a formula field in Quick Base to have that format and then change the formula field to a text field and the data for the Key field will be populated.  Then change the Key field to that concatenated field.

    Then you will be able to build a formula to be the Last year Key field and make a relationship with the last year records to bring in the data.

    Post back if you want to go ahead and do that, but get stuck along the way.