Qrew LegendThis can be done using two different approaches. The first is to make the table related to itself and look up last weeks values into this week's record.
You would be to have a scalar field in the format say
07-05-2024|MAINSTORE
where we concatenate the date with a vertical Pipe and then have the store name. If there data is being entered by hand on a from, then you would have a formula field called [Week|Store (formula)] to calculate that value and populate a field perhaps called [Week|Store (key)] using a form rule
You would initialize that field by making it a formula equal to the formula field and then changing it back to be a text field. Then set that to be the Key field of the table.
Then you would make a relationship from the table to itself and let it create a field for Related Week Store.
Then change that Related Week Store field to be called LastWeek|Store and make it a formula calculated to be the value of the previous week's date and the store (separated by a Pipe).
Then look up what you need from last week's record into this week.
The alternative is to use a formula query to get the value of last weeks KPI value. You would need to have a formula query for every KPI that you wanted to look up. Depending on the amount of data in your app you could run into performance issues and also it might be a little bit annoying to have to create a new formula query for every different KPI that you wanted to bring forward into the current record.
I can help you if you get stuck using either approach. If you do want to try the Formula Query approach them to help you I would need to know the field ID for a particular KPI field and the field ids for the store and the date.
