2 Replies Latest reply on Jul 23, 2012 5:29 PM by . Oruvan

# Calculation with Dates

I have the following data in a database

 ID Customer Sale Date Sales 1 John 1/1/2010 100 2 Frank 2/1/2010 80 3 Charlie 6/1/2010 50 4 John 9/1/2010 200 5 Diaz 12/30/2010 500 6 Charlie 10/1/2010 600

I would like to compute the sales from new customers in a specified time frame. So with the above data, I want to compute the total sales of new customers for 1/1/2010 thru 6/1/2010 and from 7/1/2010 thru 12/31/2010.

For Date Range 1/1/2010 thru 6/1/2010, Sum is \$230 (This includes Row ID 1,2,3)

For Data Range 7/1/2010 thru 12/31/10, Sum is \$599 (Only Row ID 5 which is Diaz, ignoring John and Charlie as they are repeat Customers)

It sounds simple, hope it is the same in Tableau. It will be great if I can do the above in one pass.

• ###### 1. Re: Calculation with Dates

Hi Oruvan,

One way this can be done is to place the Sale Date and Customer fields on the rows shelf. Then, create a calculated field similar to the following:

running_sum(count(Customer))

Place this calculation on the view, right click and select Compute Using-->Sale Date. Then, create a second calculated field:

if attr([Sale Date])<=#6/1/2010# and [Calculation1]=1 then sum(Sales)

elseif attr([Sale Date])<=#12/30/2010# and [Calculation1]=1 then sum(Sales) end

Place this on the view. Remove Customer from the rows shelf and place it on the level of detail shelf. Remove the first calculation from the view.

Hope this helps a little bit!

-Tracy

• ###### 2. Re: Calculation with Dates

Thank you.