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

    Calculation with Dates

    . Oruvan

      I have the following data in a database

      IDCustomerSale DateSales
      1John1/1/2010100
      2Frank2/1/201080
      3Charlie6/1/201050
      4John9/1/2010200
      5Diaz12/30/2010500
      6Charlie10/1/2010600

      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.

       

      Expected answer is below :

       

      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
          Tracy Rodgers

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

            Thank you.