4 Replies Latest reply on Apr 26, 2016 7:41 AM by Steve Taylor

    Using a calculated field for continuous date series axis?

    Brian Crawford

      Using the Super Store data set for my example, I have a calculated field that counts how many orders were open on a specific date of interest.

      The formula for [Open Count] is pretty simple:

       

         COUNTD( IF [Date of Interest] <= [Ship Date] AND [Date of Interest] <= [Ship Date] THEN [Order ID] END)

       

      In my example, [Date of Interest] is a parameter.  I can slide the parameter value across individual dates, but can't graph across a series of dates.  I would like to have the Columns Axis display a series of dates across a range.

       

      Any ideas?

        • 1. Re: Using a calculated field for continuous date series axis?
          Steve Taylor

          Hello Brian

           

          The outcome you want I think is also known as "queue length" as you want to show, for a given day, how many orders are in flight but subtract those closed the day before and add those opened that day.

           

          There are a number of solutions proposed in the thread below:

          How can you calculate queue length using Tableau?

           

          Hope that helps

           

          Steve

          • 2. Re: Using a calculated field for continuous date series axis?
            Brian Crawford

            Steve,

             

            Thanks for your reply, but I may not have been clear about what my specific concern was.  The problem I am having is not about how to perform the "queue length" calculation itself, but rather how to plot that answer across a series of dates.  The issue I have is that the [Date of Interest] values desired for plotting need to be independent of [Order Date] and [Ship Date].

             

            I would like [Date of Interest] to be all the days between a user selected start/end period within the range of MIN([Order Date]) and DATE(NOW()).

             

            I would like to have [Date of Interest] on the Columns Shelf, and [Open Count] on the rows, but I can only get [Date of Interest] to be a single date, and not all the dates within a range.

             

            My guess is that this is some sort of 'data densification' problem, but I am not strong on how that approach works.  There may be an easier way, but I am stuck.

            • 3. Re: Using a calculated field for continuous date series axis?
              Steve Taylor

              Sorry Brian, my wording wasn't very helpful!

               

              You already have the calculation for a 'given day', what I should've said is that to plot the result for all days in the dataset richard leeke's reply in that forum question I linked above shows how to do that through custom SQL and a running sum on the [Delta] value from his SQL.

               

              Unfortunately, you don't get the option to use custom SQL on excel datasources in 9.3 so it'd require reshaping the data in excel to create two tables (one for start dates where start date = order date and one for end date where end date = shipped date) and union them together using the Union feature in the data connection screen.

               

              I can mock this up tonight if you'd like a working example.

               

              Steve

              • 4. Re: Using a calculated field for continuous date series axis?
                Steve Taylor

                Found some time to provide an example of what i meant, hope it's what you had in mind!

                 

                The attached twbx has a union datasource which I created by copying the first few columns of the Orders worksheet from sample - superstore into two separate sheets OrdersStart and OrdersEnd (could've combined in excel but wanted to show the union feature).

                 

                I then added an EventDate column and EventType and for respective sheets set these fields to order date and order / ship date and ship.

                 

                Using the union feature I appended those together and created a "Delta" field that'll be used to say how many orders are open (ordered not shipped) for each day.

                 

                This calculated field is:

                 

                IF [EventType] = 'Order' THEN 1

                ELSEIF [EventType] = 'Ship' THEN -1

                END

                 

                Which essentially says, if there's an order, add one, if an order has been shipped, subtract one.

                 

                Showing the running sum of this field (as shown in the workbook) across EventDate will show the volume of open orders each day.

                 

                please get in touch if this doesn't help.

                 

                Thanks

                 

                Steve