Discussions

 View Only
  • 1.  YOY % of This Year and Last Year

    Posted 09-15-2020 14:33
    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?
    Looking to calculate YOY% Change for each Column Group


    ------------------------------
    George Bramhall
    ------------------------------


  • 2.  RE: YOY % of This Year and Last Year

    Posted 09-15-2020 16:30
    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
    ------------------------------



  • 3.  RE: YOY % of This Year and Last Year

    Posted 09-15-2020 17:48
    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
    ------------------------------



  • 4.  RE: YOY % of This Year and Last Year

    Posted 09-15-2020 22:50
    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


     







  • 5.  RE: YOY % of This Year and Last Year

    Posted 09-16-2020 07:19
    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
    ------------------------------



  • 6.  RE: YOY % of This Year and Last Year

    Posted 09-16-2020 07:31
    Blake,
    So If I am measuring total shipped for each product I have to make a summary field for total shipped for each product and I have to remember to add a summary field when I have a new product?
    GB

    ------------------------------
    George Bramhall
    ------------------------------



  • 7.  RE: YOY % of This Year and Last Year

    Posted 09-16-2020 14:16
    George -

    To accurately answer your question, I would need to know a little more about your application structure. If you'd like, I'd be happy to jump into Slack or Skype and chat with you a little more real-time. 

    But, based on what I know so far, I would suggest a setup like this:



    ------------------------------
    Blake Harrison - DataBlender.io
    Quick Base Solution Provider
    ------------------------------