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 ----------...
Forum Discussion
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
------------------------------
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
------------------------------
- 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
------------------------------- George -For the structure, I see it as this:Years < ProjectsYears can have many Projects - or whatever you're measuringYears < YearsThis 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
- Hi Blake,
Your response was very helpful, but a lot of work to get this report. You can see why users use Tableau or PowerBI for their reporting out of Quick Base.
------------------------------
George Bramhall
------------------------------
