Forum Discussion

GeorgeBramhall1's avatar
GeorgeBramhall1
Qrew Member
5 years ago

YOY % of This Year and Last Year

Anyone have an idea how to show (calculate) a YOY % change from 2019 to 2020 for each Heading (e.g AGHM) in this Summary Report?


------------------------------
George Bramhall
------------------------------
Relationships
reports and charts
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    While you can accomplish this with a single table, I would not suggest it. In order to do a proper comparison on a value at different time periods - year over year, month to month, etc - you will need a table that represents the time period you are reporting on, Years for example. Each record on this table would represent a single Year and would need to be connected via a Relationship where Years would be the Parent table. Once you have this setup, you can build Summary fields up to the Year record for each field you would like to report on.

    Once your basic structure is in place, you can build additional circular relationships on your Years table for Prior Year, 2 Years Prior, or whatever timeframe you need to compare. You would then bring over those same fields as Lookups from the Prior Year record and build formulas to calculate your YOY values.

    ------------------------------
    Blake Harrison - DataBlender.io
    Quick Base Solution Provider
    ------------------------------
    • GeorgeBramhall1's avatar
      GeorgeBramhall1
      Qrew Member
      5 years ago
      This occured to me but I would have to create a formula field to be the year in the child table and QB does not allow summary fields where a formula field is used as a reference field. 

      Also when you say circular relationships are you saying
      Parent      Child
      Table1 -> Table 2 for Current Year
      and 
      Table 2 -> Table 1 for Prior Year Comparison

      ------------------------------
      George Bramhall
      ------------------------------
      • BlakeHarrison's avatar
        BlakeHarrison
        Qrew Captain
        5 years ago
        George -

        For the structure, I see it as this:

        Years < Projects 
        Years can have many Projects - or whatever you're measuring
        Years < Years
        This is a circular relationship where the same table is the Parent and Child. While this would allow for the possibility of having 1 Year with multiple Child Years underneath it, you would never really have that as you would want to set this reference field as the 'Prior Year'

        One note on this, for the Years table, set the Year field as the key for that table.

        Regarding your concern about needing to use a formula field for the Year in the Child table, you are correct in that this can cause trouble if you use the formula field as the reference field. To get around this, you would still create a formula field, but call it 'Year Formula' or something like that. Your 'Year' field would then just be the standard reference field and you could use a Form Rule or an Action or Automation to move the value of the Formula field to the Reference field.

        Blake Harrison

        Owner / Quick Base Solution Architect

        Phone / SMS: 404.800.1702