4 Replies Latest reply on Nov 6, 2018 8:27 AM by Sasha Hanna

    How to Set Up Active Orders

    austin.somlo

      I have a dataset with 3 columns: ID, Start, and End.

       

      What I want to do is to create a bar chart that will show active orders per month. What I mean by active orders is it's still open for the month.

       

      Example:

      If I have 4 orders as follows:

      1 = 9/12/2017 to 1/15/2018 (I want to count Sept, Oct, Nov, Dec, and Jan as 1 order per month; afterwards, it's 0 the rest of the way for not being active anymore)

      2 = 11/28/2017 to 3/5/2018 (I want to count Nov, Dec, Jan, Feb, and March as 1 order per month; afterwards, it's 0 the rest of the way for not being active anymore)

      3 = 12/30/2017 to (I want to count Dec, Jan, Feb, March, and so on as 1 order per month; this is still continually open since it has no end date)

      4 = 7/12/2017 to 11/15/2018 (I want to count July, Aug, Sept, Oct, and Nov as 1 order per month; afterwards, it's 0 the rest of the way for not being active anymore)

       

      My ultimate goal is to have a bar chart shows total number of active orders per month:

       

      July = 1

      August = 1

      Sept = 2

      Oct = 2

      Nov = 3

      Dec = 3

      Jan = 3

      Feb = 2

      March = 2

      April = 1

      May = 1

      etc.

       

      I provided an attachment. Can you show me how to accomplish my goal? I am using Tableau Professional v10.5. Thank you.

        • 1. Re: How to Set Up Active Orders
          Zach Leber

          Search the forums for ticket counting, e.g. Weekly Ticket Backlog Tracking

          You will need to re-shape your data using Custom SQL, you can do this in Tableau.

          Post an updated TWBX when you get closer.

          • 2. Re: How to Set Up Active Orders
            Sasha Hanna

            Hi Austin,

             

            To create such a graph you will need to wrangle the data. A technique to do this is called scaffolding where you join a date field to fill in the dates between your start and end for each order, thereby creating duplicate rows of data. You must ensure you set a condition where the new date is greater than the start and smaller than the end.

             

            I highly recommend you do this with prep and watch this video to help you do so: Solving tough time-based problems with skeleton tables and Tableau Prep - YouTube

             

            Cheers,

            Sasha

            • 3. Re: How to Set Up Active Orders
              Zach Leber

              Scaffolding is only necessary for ticket counting if your row data is sparse compared to your bins. In this case we can use Custom SQL to set a counter whose running sum will be the number of open tickets in any month.

               

              SELECT [ActiveOrders#csv].[Start] AS [Date], +1 as [Count]

              FROM [ActiveOrders#csv]

               

              UNION ALL

               

              SELECT [ActiveOrders#csv].[End] AS [Date], -1 as [Count]

              FROM [ActiveOrders#csv]

               

              Custom SQL for files is only available if you select Windows legacy connection when opening them but if you're source data is in a database you won't have that limitation. I exported the Hyper data to a CSV and opened that.

               

              I also added a table filter instead of a regular date filter so you can set your date range without messing up your running sum. See 10.4 TWBX attached. You won't be able to edit the data source unless you put the attached CSV somewhere you can access from Windows. The second date filter excludes nulls in order to handle tickets that are still open so as to not decrement the count.

              • 4. Re: How to Set Up Active Orders
                Sasha Hanna

                Hi Zach,

                Thanks for clarifying. I tend to avoid recommending the use of custom SQL because it has a high impact on dashboard performance. It is always better to provide Tableau with a data set that is ready for analysis, and push back such manipulations back to the data source level.

                Cheers,

                Sasha