Forum Discussion

RandyJones's avatar
RandyJones
Qrew Member
3 years ago

Week to Week Comparisons

I have records that occur daily and have created a WeekofYear field to group each weekly set of records. I am trying to compare the number of records that occur each week to determine a percent of increase or decrease of the set of records from week to week. I have a summary report that groups by week with the total of records but I am not sure how to do the comparison.

------------------------------
Randy Jones
------------------------------
Relationships

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    I suggest that you make a new table called year weeks. The key field of this table should be set in the format YYYY-01 so like
    2021-01
    2021-02
    etc

    Use excel to load this up  

    The create a calculated field in your data to calculate that same string and make a relationship to count the # of records each week.

    You will then be able to relate the Year Week table to itself by calculating the value for the previous YYYY-WK, for exampke if a record is 2021-12, then the previous week formula would calculate to 2021-11.

    Then you can use that calculated field to relieve the table to itself and look up the values from the previous week into the next  week​ the calculations.

    I know that sounds like a lot of hoops to go through to do something that seems like it would be simple to do in excel. There is something new coming this fall called Formula Queries and using this new tool it would be much easier to do and you would not have to set up the extra helper table and the relationships.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • RandyJones's avatar
      RandyJones
      Qrew Member
      3 years ago
      I built the table and created the join but I am a little hazy on the formula to compare the two weeks. One example of what I want to compare is the number of records from one to week to the previous week.

      ------------------------------
      Randy Jones
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        Can you confirm that the Key field of the table is the YYYY-MM and also is it in that format like 2021-09 for September?

        OK, assuming that is true, we need to make a field to connect to the the table to itself to access the previous month. 

        This  should work to calculate the previous month's YYYY-MM

        var number YYYY = ToNumber(Left([YYYY-MM],"-"));
        var number MM = ToNumber(Right([YYYY-MM],"-"));

        var number PrevYYYY = If($MM=1, $YYYY-1, $YYYY);
        var number PrevMM = If($MM=1, 12, $MM-1);

        List("-", ToText($PrevYYYY), PadLeft(ToText($PrevMM), 2,"0"))


        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------