4 Replies Latest reply on Mar 22, 2012 8:17 AM by keith.mccarthy

    Create net change by period line chart

    keith.mccarthy

      Hello,

       

      I unsuccessfully attempted to find this in prior postings. 

       

      I wish to create a line chart for purchase order data that shows three lines: 1) Items opened by period, 2) Items closed by period, 3) the Net Change of open Items, and 4) the total open Items for that period.  Below is a mockup of the goal chart.

       

      I can easily create separate charts for the opened and closed, but not together.  Because each Items has an open date and some have close dates, I am unsure how to depict the counts in separate periods.  Placing the Open Date as a column formatted by month/year groups the open

       

      In Excel I create the dates, then count the dates that are between the date ranges for each cell, similar to below

       


      CreatedClosedNetOpen
      3/31/20121,880 2,110 (230)10,758
      2/29/20122,656 4,046 (1,390)10,988
      1/31/20122,739 3,124 (385)12,378

       

      Further compounding the issue, the items are not unique as they may be split into multiple rows by department.  However, any given item is only counted once.  Normally, a COUNTD() solves this, but I cannot make it work in this instance because of aggregations.

       

      So, how do I create a similar view in Tableau?  I'm at a loss...

        • 1. Re: Create net change by period line chart
          Alex Kerin

          I'm sure this can be done, but very difficult to help you without the seeing the data. Could you post a workbook, with mocked up data if necessary?

          • 2. Re: Create net change by period line chart
            keith.mccarthy

            Hi Alex,

             

            Attached is a file with mock data.  Thank you very much for your help!

            • 3. Re: Create net change by period line chart
              Alex Kerin

              Interesting issue because this is a queue issue. I've seen this before attacked using custom SQL - the approach I took. Basically we reshape the data using custom SQL to do two things, pull open and closed date into one column, and use [Net] as both the indicator of whether this is a created date or closed date and as the net change in open cases:

               

              SELECT [Sheet1$].[Date- 6- Created- Line] AS [Date],

                1 as [Net],

                [Sheet1$].[Item #] AS [Item #],

                [Sheet1$].[Item/ Line] AS [Item/ Line],

                [Sheet1$].[Line] AS [Line]

              FROM [Sheet1$]

              union all

              SELECT

                [Sheet1$].[Date- 7- Closed- Line] AS [Date],

              -1 as [Net],

                [Sheet1$].[Item #] AS [Item #],

                [Sheet1$].[Item/ Line] AS [Item/ Line],

                [Sheet1$].[Line] AS [Line]

              FROM [Sheet1$]

               

              This works fine and as you can see matches your expectations from Excel.

               

              I duplicated the source and used select distinct in the custom SQL to remove the dupes from the file to get the correct numbers in the last two sheets. Note, I have explicitly set the running sum table calculation to sort along ascending date - as your data had the last month first, we couldn't use a simple table (down) calculation - this way is more robust anyway. There are also 198 cases that are not closed. Excluding the null closed dates from the 3rd and 4th sheets allows these to be counted correctly. If your actual data source has the departments in as well, and you need to bring them over, there will need to be different calculations.

              • 4. Re: Create net change by period line chart
                keith.mccarthy

                Hi Alex,

                 

                It took some time to parse and understand the file.  I really like your approach! 

                 

                I do have one question.  By using select distinct, it creates a record based on all items included in the query, right?  Would it be more accurate to create one select distinct with only the item/ line number and then a left join based on item/ line number and pull the Min or Max of the given dates?  That would ensure perfect distinctness.

                 

                What do you think?