## Forum Discussion

MarkShnier__You

4 years agoQrew Legend

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

------------------------------

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

------------------------------

- 4 years agoI 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

------------------------------ ReplyReply - 4 years agoCan 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

------------------------------ ReplyReply - 4 years agoYes, 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

------------------------------ ReplyReply Show More