Get a value from another entry in the same table
We've been using a QB Table to track snap shots of business health. Once a week we log some numbers across different tables. This isnt exactly what we do - but as an example it could be:
- how many orders placed
- how many orders delivered
- how many orders on hold
- how many customers outstanding on payment
- how much billed received
- how many customer feedback received
- etc etc
and we do this for each "store" - let's assume we have 2 stores.
Now here is my issue!
I want to track PERCENTAGE CHANGE since last week.
So it's the same table - but I want to compare, "orders placed" this week for this store, versus "orders placed" last week for the same store. I want to be a formula for a field which would show PERCENTAGE INCREASE OR DECREASE. ( and then we can get fancy and make charts, graphs, color code etc)
Where I am having the issue is - how do I find the value of last weeks entry for this store?? let's assume each entry has a "week of" and a "store code" - so for example: 07-05-2024 and MAINSTORE. How do I get the # of orders placed for 07-28-2024 and MAINSTORE and have it auto populate?
I would prefer to go this route where I can lookup last weeks #'s - so if I ever need to scale I can. Worst case is that I will choose 2-3 KPIs to log each week.
Any ideas?