Week to Week Comparisons

Question

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
------------------------------

MarkShnierYou · Answer

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
------------------------------

Sign up

Login to the community