2 Replies Latest reply on Sep 11, 2018 7:06 AM by AYMERIC POIGNON

    Orders Waiting list

    AYMERIC POIGNON

      Hello There,

       

      I use Tableau since several months, great software !

      But for the first time I'm faced with a challenge I don't really know how to solve. Maybe someone can help ?!

       

      Here's the thing :

      I have multiples Orders (OR1, OR2, OR3 etc...), each order has two dates : a creation Date and a Final Date.

      So it's a simple tableau with 3 columns (Order, Creation, Final) and 3 Dimensions.

      Plot twist : when an order has no final Date, it means the order is not finished and part of a waiting list.

       

      Problem :

      I would like to see the evolution of the waiting list, like a histogram showing me how much orders are in the waiting list each day for the past year.

      Indeed, when I count all the missing Final Date I only have the waiting list the day I extracted the data (sounds normal).

       

      Part of the solution (according to me) :

      -I created a parameter as a date

      -I created a calculated field which returns TRUE when the creation Date < Parameter (the order has to be created) AND Isnull(Final Date)=TRUE OR Datediff(Parameter, Final) > 0 (no final date, or final date is greater as the parameter)

      This is functional because it returns the number of Orders in the waiting List for the date of the parameter. It works !

      However, as I can't make a variable parameter in the window, I can't make a histogram and see the evolution with a graph. I have to manually change the parameter.

       

      I tried several things, but nothing works.

      If someone has already met this problematic, please share !

       

      Thank you very much !

       

      Aymeric

        • 1. Re: Orders Waiting list
          swaroop.gantela

          Aymeric,

           

          Proposed is a generic attempt at your question,

          though it may need adjustment in practice if your dataset is large

          or if your dataset type doesn't allow for such methods.

           

           

          The idea is to join your data with a running column of dates

          to be used as the x-axis for your histogram.

          Then you can compare your Creation and Final dates to

          the LookupDate to see if it meets the criteria for the Waiting List.

           

          This method is generally described here:

          CROSS JOIN with Tableau's join dialog

           

          An example of a similar implementation of this in Tableau Prep is shown here:

          Re: Seems like it should be basic

           

          In your example, I made a new sheet of a single column dates.

          I joined this to the data with a calculated field of 1.

          This joins every date to every data row.

          I filtered the data down using:

          [Look Up Date]>=[Creation Date]

          AND

          [Look Up Date]<=[Final Date]

           

          Then the criteria to count for the waiting list became:

          IF ([Look Up Date]>=[Creation Date]  AND  [Look Up Date]<[Final Date])

          THEN 1 ELSE 0 END

           

          Please see the workbook v10.3 and datasource attached in the Forum Thread.

          The dashboard has the histogram on top and then a gantt sheet with a parameter

          to check the values.

           

          Further in depth discussion of this method, performance considerations, and alternatives

          can be found here:

          Calculating ADC in Tableau Server Professional Edition 10.5.2

          1 of 1 people found this helpful
          • 2. Re: Orders Waiting list
            AYMERIC POIGNON

            Hello Swaroop,

             

            Sorry for the late answer, I spent several days trying your solution.

             

            This solution works indeed, I can have my orders waiting list at the end.

             

            But :

            - It's not really effective for big database like mine. Indeed, It multiplies each line by the number of date you want to compare, in my case it's really huge

            - You have to join your data with a custom range of date Look up. (not really the best solution in my case : the database can't be modified by myself or I will need an extra excel file with the look up dates).

             

            I would rather be interested in a solution 100% inner tableau with calculated fields / parameter / filters, and no works on the database or joining of tables.

             

            Anyway that helped and I know I can do this way in last resort.

             

            Thank you !

             

            Aymeric