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 inc...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendI 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
------------------------------
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
------------------------------
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
------------------------------
------------------------------
Randy Jones
------------------------------
- MarkShnier__YouCan 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
------------------------------- Yes, I have a table with the Key field of YYY-MM and formatted as you show. I also created a previous month's field with the formula you included (it's in the data table - not the Year Week table). Now I need to calculate the difference between a month's record count and a previous month record count. I know I am asking you to basically walk me through each step but as this is new to me I really appreciate the help!
------------------------------
Randy Jones
------------------------------- MarkShnier__YouOK np,
Now make a relationship between the YYYY-MM table and itself. The system may ask "are you sure", but just carry on. When it asks for the reference field, do not let it make a new field, but rather choose the field for [previous month YYYY-MM]
Then lookup any values that you need from the previous month to the YYYY-MM record and now you have two clean fields for say the [# of records] for the YYYY-MM month and the previous YYYY-MM [# of records for Previous YYYY-MM] sitting on the same record and you can do your calculation.
FYI
There is something amazing and new about to be released to general usage call Formula Queries. Its now in Open beta which means you have to ask for the feature. When we get that feature it would not be necessary to go through these jumps to build that helper table and do this relationship to itself.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------