2 Replies Latest reply on Oct 18, 2016 7:02 AM by Frank-Michael Idrees

Calculation: Consindering the latest record only

Hi Tableau Community,

Apologize first for the title but I don't know how this kind of issue is called.

So here the problem:

I have these two simple tables (Sales Orders and Purchase Orders), the link between these tables is the Inventory ID:

First of all I want to count the number of Sales Orders. If I use just the default number of records it wil show 14 but with the use of CountD function on SO IDs  I'm getting the correct 10 Sales Orders. This part is easy but when it comes to calulate the Margin (which is basically the Sales Amount minus the related Purchase Amount) it will consider all related POs. But in my case I just want to consider (in case there are multiple POs related) the latest PO (=the latest PO ID) for the Margin Calculation. So for example: SO ID 1 (Inventory ID: 10) has a sales amount of \$1,100 and there are 3 related PO IDs (10/20/120) with different 3 purchase amounts (1,500/1,400/1,000). I want only to consider ther MAX PO id (120) for the margin calculation which hast the Purchase Amount of 1,000 and the correct Margin of 1,100-1000=100.

Actually I tried it with some not very efficient workarounds (Index/Filters etc.) but there must be a much better way. It would also help if someone knows how this kind of problem actually is called, I'm sure this is a basic issue when dealing with databases.

Thank you very much in advance.

• 1. Re: Calculation: Consindering the latest record only

Hi,

A quick question. Which data source are you using? I'm assuming you're using excel.

You can get max PO using below mentioned calculated field

Then create a calc field to get the margin.

Workbook has been saved in my public profile. Let me know If you've any question.

Mahfooj

• 2. Re: Calculation: Consindering the latest record only

Sorry for late reply, but your suggestion works fine. Thank you so much