4 Replies Latest reply on Dec 7, 2015 4:03 PM by Manikanta Gudipati

    Inventory Levels - Issues With Timeline.

    Manikanta Gudipati
    Manikanta Gudipati Nov 24, 2015 11:49 AM

      Hi,

      This is my first time using tableau forums to post a question. Excuse me if I am unclear.

      Jonathan Drummey, in the past, I found many answers by referring to your posts\replies. If you could help me figure out a solution for the problem mentioned below, it would be great.

      In my data set, I have two data fields named Start Date and End Date for work orders. Using these, I want to calculate:

      • - Total no. work orders that came in – by month
      • - Total no. work orders that were completed – by month
      • - Running Total no. work orders that are pending – by month (Cannot accomplish this)

      To calculate the total no of work order that came in every month, I created:

      • - Date_Start calculated field based on Start Date -> DATETRUNC('month',[Start Date]) and another calculated field named NewInv_Test -> SUM(IIF(DATEPART('month',[Start Date])=DATEPART('month',[Date_Start]),1,0)) that counts the total number of orders that came in.

      To calculate the total no of work order that were completed in every month, I created:

      • - Date_End calculated field based on End Date -> DATETRUNC('month',[End Date]) and calculated field named EndInv_Test -> SUM(IIF(DATEPART('month',[End Date])=DATEPART('month',[Date_End]),1,0)) )) that counts the total number of orders that were completed in each month.

      Then, I created two sheets – one with Start Month on the X axis and the other with End Month on the X axis.

      The problem here is, since there is no common axis (date) for orders coming in and orders being completed, I am unable to calculate a running total.

      I was able to create the dashboard with running total based on table calculations for orders coming in and orders completed using Excel table calculations. I want to replicate this in Tableau.

      Attached are the following:

      Inventory_From Dates -> the workbook I am having problems with

      Inventory Management -> the workbook I created based of a table with calculations in Excel

      Example of Inventory calculation -> data source for Inventory_From Dates. Also has the table calculations for Inventory Management

      Inventory Data Reshaped -> Sheet with final table from Example of Inventory calculation. Also, data source for Inventory Management workbook

       

      Thanks

        • 1. Re: Inventory Levels - Issues With Timeline.
          Jonathan Drummey
          Jonathan Drummey Ambassador Dec 1, 2015 8:29 AM (in response to Manikanta Gudipati)

          Hi,

           

          Are you still looking for help with this?

          • 2. Re: Inventory Levels - Issues With Timeline.
            Manikanta Gudipati
            Manikanta Gudipati Dec 1, 2015 8:32 AM (in response to Jonathan Drummey)

            Hi Jonathan,

            Yes. I am still looking for help with this!

            • 3. Re: Inventory Levels - Issues With Timeline.
              Jonathan Drummey
              Jonathan Drummey Ambassador Dec 4, 2015 12:17 PM (in response to Manikanta Gudipati)

              For this view I used a variation on the techniques described in http://community.tableau.com/message/191608#191608. The data is in a shape that enables lookups and comparisons, but isn't in a transactional form that lets us count the numbers of new starts & ends. See http://redheadedstepdata.io/lookup-vs-transactional/ for a good overview. Additionally the transactional data is sparse - there are no new starts or ends for December.

               

              To change the data from the lookup-oriented structure with start & end date columns to a taller transactional format I used Tableau's Pivot feature, then I named the two new columns Start/End and Date.

               

              In order to deal with the sparseness of the data so we can show an inventory count for December even though there are no new starts or ends I decided to use a "scaffold" data source. This data source only has the months, but it has all the months we want to report on and will be used as the primary data source in a Tableau data blend.

               

              Then I built a Month custom date in the pivoted Inventory source to be the linking dimension.

               

              Then I created three measures in the Inventory source:

               

              //New Starts
CASE [Start/End]
    WHEN "Start Date" THEN 1
    ELSE 0
END

//Ends - this uses FLOAT() to enable axis synchronization in the final view
FLOAT(CASE [Start/End]
    WHEN "End Date" THEN IF ISNULL([Date]) THEN 0 ELSE 1 END
    ELSE 0
END)

//Inventory - adds one for each start date, subtracts one for each end date
CASE [Start/End]
    WHEN "Start Date" THEN 1
    WHEN "End Date" THEN IF ISNULL([Date]) THEN 0 ELSE -1 END
END

               

              The Inventory field is turned into a Quick Table calculation Running Total with a Compute Using on the Month in the workout worksheet:

               

              2015-12-04 15_14_22-Tableau - inventory levels.png~~~pic

               

              Then to build the initial view we can duplicate the worksheet to move pills around, set up the dual axis, synchronize the axes, and assign colors:

               

              2015-12-04 15_14_53-Tableau - inventory levels.png~~~pic

               

              And finally the right-hand axis can have Show Header turned off and the nulls indicator can be hidden, and grid lines added:

               

              final view.png

               

              If you have any questions, let me know!

               

              Jonathan

              • 4. Re: Inventory Levels - Issues With Timeline.
                Manikanta Gudipati
                Manikanta Gudipati Dec 7, 2015 4:03 PM (in response to Jonathan Drummey)

                Hi Jonathan,

                 

                Thank you for taking a look this. It works great.

                 

                I would also like to create a similar view using Tableau 8.2 with Tableau Server as the data source. Since Pivoting is not an option with Tableau 8.2, I decided to create a view that shows no. of open orders between two selected dates. To achieve this, I created two parameters that serve as inputs for date range and then a Boolean calculated field to filter open orders.

                 

                That aside, Would it be possible to create a view that shows no. of open orders for all the month using 8.2 with Tableau server as the data source?

                 

                Thanks