Forum Discussion

GeoffBarrenger's avatar
GeoffBarrenger
Qrew Captain
3 months ago

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?

formula query
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 months ago

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