1 Reply Latest reply on Feb 29, 2016 10:04 AM by Dan Sanchez

    Calculating rate or % of Shipment over Order using two different Date field

    Anita B

      Hi ,

       

      I have a requirement to calculate the rate of shipment, month on month basis over a period of Fiscal year. I have number of products with Order date and  I have number of products with shipment date.

      I need to calculate rate of Shipment over order for each month. Currently I am extracting the running sum of Shipments for each month and running sum of orders for each month and then calculate the  rate by dividing the values manually. I want to automate the process in tableau. Because I do not have common month I am having problem to automate the process using Tableau calculated field.

      I have attached sample excel data source sheet.  I am using Tableau 8.1.

       

      Rate of shipment over Order =(  running_sum(no of prod) for each month of shipment date) / ( running_sum(no of prod) for each month of order date)

      The extracted excel is date values are similar to below data

       

      MonthRate
      Jun-1520%
      Jul-1535%
      Aug-1557%
      Sep-1565%
      Oct-1577%
      Nov-1579%
      Dec-1592%
      Jan-1684%
      Feb-1690%

       

      The expected Graph is as below

       

      Is there any idea how can plot the graph without extracting data to another excel sheet but using the same base sheet superstore sample sheet.

       

      Thanks,

      Anita.

        • 1. Re: Calculating rate or % of Shipment over Order using two different Date field
          Dan Sanchez

          Hi Anita!

           

          I've attached a workbook using your sample data that I think let's calculate the Rate of Shipment all within Tableau.  There are a number of steps required to achieve the results though.

           

          1 - Duplicate the data source.

          We'll be using one copy of the data source to find the number of orders Ordered and the other copy of the data source to find the number of orders Shipped.  I've renamed the data sources to help clarify this

          2-29-2016 9-59-11 AM.png

           

          2 - Create a calculated field in each data source called [Date].

          In the Order Date data source the calculated field will call [Order Date].  In the Ship Date data source the calculated field will call [Ship Date].  The reason for naming both fields [Date] is that this lets Tableau automatically blend the data sources on this field.

           

          3 - Create running sum calcs.

          In the Order Date data source we need a calc using this formula:

          RUNNING_SUM(COUNTD([Order ID]))

           

          In the Ship Date data source we'll need a similar calc with this formula:

          RUNNING_SUM(COUNTD([Order ID]))

           

          (yes, both formulas are exactly the same, the magic will happen when we do the data blending)

           

           

          4 - Create the Rate of Shipment calc

          I created this calc in the Order Date data source that divides the two calcs we created in step 3

          2-29-2016 10-02-55 AM.png

           

          5 - Create the view

          2-29-2016 10-03-20 AM.png

           

          I did some additional formatting by setting the Fiscal Year start for all the date fields to be in June, as well as just filtering to a single Fiscal Year.  I also set the number formatting for our Rate of Shipment calc to be a percentage. 

           

          Hope this helps in getting your analysis started!

           

          Thanks Anita!