Forum Discussion

EmberKrumwied's avatar
EmberKrumwied
Qrew Captain
4 years ago

Compare rows of data in same table

I have a table that tracks 8 different values for a specific item.  A specific item may/will have multiple rows of data.  The table is updated annually to record new values for the item for a given year.  What I need is a way to report on the % change for each item for any 2 given years.

Table A (as the following fields)
Record ID# (key field)
Item Number (links to a parent table)
Year
Value1
Value2
Value3
Value4
Value5
Value6
Value7
Value8

For each item number I need a way to specify 2 years (i.e. 2022 & 2021), have the values for those years returned for each item, then show the % change between them (i.e. item number #1: % change of Value1 from 2022 and Value1 from 2021).

Each item number could potentially have value information that spans multiple years, but I will only need to compare a single year with another single year.  In Access I accomplish this via queries, but am at a loss as to what the equivalent solution would be in QuickBase.​

Any direction on how to get started would be appreciated.​

------------------------------
QuickBase Admin
------------------------------
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    4 years ago
    I'm going to assume you'd be looking at these values for either the Current and Prior years or the Prior and Prior -1 years. Based on that, on the Item Parent, I would first create 2 Formula Numeric fields that would represent the 2 years you are looking to compare. Then, take these values down as Lookups from the Parent down to the Item-Year record.

    Next, you can go one of two ways:
    Option 1:
    • Create 2 Summary fields - one for each of the 2 years - that would be a Max on the Record ID# where [Year Lookup field] = [Year].
    • Create 2 new Relationships from the Item to the Item Year record, with the Item Year record being the Parent and using the Max Record ID# from the previous step as the Reference field.
    • In each relationship, create Lookup values for each of the fields you would like to compare
    Option 2:
    • Create Summary fields for each of the fields you would like to compare where [Year 1 Lookup field] = [Year].
    • Create Summary fields for each of the fields you would like to compare where [Year 2 Lookup field] = [Year].
    They both have their pros and cons, but really, either way should work for what you're trying to do.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------