6 Replies Latest reply on Dec 4, 2015 12:51 PM by Bin Xiao

# How to get the data by max date and then sum it up?

Dear all,

I am struggling on the following calculations: (I am using Tableau 9.0 version and using Live data)

Item NumberDateEnd QuantityCost
ABC-19/3/1510010
ABC-19/25/158010
CDE-310/9/1512020
CDE-311/3/1518018

So, what I want to achieve is:

(1) Get the "End Quantity" and "Cost" for each item by max (most recent) date:

In this case, for item ABC-1: since the max date is 9/25/15, the end quantity I want to use is 80 and cost I want to us is 10

for item CDE-3, since the max date is 11/3/15, then end quantity I want to use is 180 and cost I want to us is 18;

(2) Then calculate the total cost:

Total cost of item ABC-1 + Total cost of item CDE = 80*10 + 180*18  = 4040

(3) In Tableau, I want to show that 4040 as a single field (that means, in my dashboard, it will have a "Total Cost" field which shows 4040)...

Thank you very very much for your help!!!

• ###### 1. Re: How to get the data by max date and then sum it up?

hi Bin,

Can I just check, if you are using Tableau 9?...it's just because this is much easier with LoDs

• ###### 2. Re: How to get the data by max date and then sum it up?

Hello Simon,

Thank you so much for your response!!

Yes. I am using Tableau 9! and Live data instead of Extract.

Thank you!

• ###### 3. Re: How to get the data by max date and then sum it up?

...nice.

So we can use LoDs here. With an FIXED LoD calc, the calculation is performed, aggregated, to the level we specify (off canvas) and then that is returned to every row, again at the specified level.

In this example, I've split the calculations into separate calculations, for explanation, but you can just nest them all into one in your final solution.

So to get the last date for each [item_number], I create the calculated field [Max Date by Item] as

{FIXED [Item Number]: MAX([Date])}

We can then use this (I've done this for cost, but the same logic applies to quantity) field to create the following calculation [Last Cost by Item] as

IIF([Date]=[Max Date by Item],[Cost],NULL)

and now that field only contains a value for that last date, which can be further aggregated.

Hope that helps and solves your problem

1 of 1 people found this helpful
• ###### 4. Re: How to get the data by max date and then sum it up?

Good afternoon

Simon just gave the answer that I just found.

Through a calculated field having the formula:

IF [Date] = {FIXED [Item Number]: max ([Date])} THEN

[Cost] * [End Quantity]

END thank you

Fabio Idalgo

1 of 1 people found this helpful
• ###### 5. Re: How to get the data by max date and then sum it up?

Thank you so much Fabio!!!! Your formula works!!!!!

• ###### 6. Re: How to get the data by max date and then sum it up?

Thank you soooooo much Simon!!!!!