Calculating Dollar Value Shift from two similar queries

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
I think this is an insanely easy problem to solve, but I am not positive how to go about it correctly.

Here is the scenario. I pull the same report every day. This report compares the previous day's report, stored in Table1, and compares it to the current day's report Table2. This report has an order ID and a dollar value. I need to have a new query that will find the same order ID from the previous day and then see the shift in dollar value. I only want to include OrderIDs that are on both. So for example

Table1
OrderID $
12345 5
23456 6

Table2
OrderID $
12345 4
23456 7


So the query would populate,

Query1
OrderID $
12345 -1
23456 1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Just create a query between the two tables, joining them on the Order ID field.
Add the Order ID field from either table, and then add the following calculated field to it:
Code:
DollShift:[Table2].[DollValue] - [Table1].[DollValue]
Where "DollValue" is the name of your field holding the dollar value (I am pretty sure it isn't "$", as I don't think that is a valid field name).
 
Upvote 0
Thanks! I indeed tried that and it didn't work initially. I must've made a simple mistake somehow.

Follow up question.. If for example one of the Order ID fields comes up twice with different S values, is there a way to only have it calculate on the first occurrence? I didnt realize there would be duplicate Order IDs within the same table, and it is not giving the desired outcome because of it..
 
Upvote 0
If the details are exactly the same, you can combine them together.
Just create a new query based on that single table, and click on the Totals button (looks like a Sigma). This will "Group" all records with similar data together.
Then, create your query between this query and the other table.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top