4 Replies Latest reply on Oct 15, 2013 4:22 PM by David Rowe

    Using a date parameter as a column?

    Trent Johnson

      I have a data source that contains "Jobs" that a department is and has worked on. Each job has a start date and an end date. I want to show, over time, the current opened jobs for each user. I've created a calculation that will show me the current opened jobs on a "specific" date, but not over time. Here is the formula:

       

      IF([startdate] <= [Up To Date] AND ([status] == "Open" OR [enddate] > [Up To Date])) THEN 1

      ELSE 0

      END

       

      The [Up To Date] is a parameter that I use to dynamically select "one" specific date. Basically if I selected Oct. 1st, 2013. I want to see a count of all jobs that were started before or on Oct. 1st, 2013 AND are still open, or were closed AFTER Oct. 1st, 2013.

       

      This formula works perfectly, but my problem is that I can't see this over time; just one specific date at a time. I want to use all possible values for [Up To Date] as a column and see the open jobs fluctuate as time passes.

       

      Would someone be able to point me in the right direction or perhaps have a technique I could use to achieve this?

       

      Thank You,

      Trent

        • 1. Re: Using a date parameter as a column?
          Joshua Milligan

          Trent,

           

          My best guess (without seeing your data) is that you are working with sparse data (a record containing a start date and an end date, but no records for dates in between).  The parameter has a single value at any given time, so it won't be of any use to give you multiple column headers or more than a single point on an axis.

           

          You will need to pad the data in some way (there are various techniques) in order to get column headers or an axis range. Would you share a packaged workbook (.twbx) with mocked-up data to simulate your specific case?  That would greatly help the community in answering your question.

           

          Regards,

          Joshua

          • 2. Re: Re: Using a date parameter as a column?
            Trent Johnson

            You are correct about the data only having a start and end date. I've attached a sample workbook showing how I am currently visualizing my data. I look into "data padding". Thank You!

            • 3. Re: Re: Re: Using a date parameter as a column?
              Trent Johnson


              I was able to get the results I wanted, but I was unable to do it entirely in Tableau. Attached is what I wanted as the end result. In order to create this viz, I had to create an entirely new table, querying day-by-day extracting the current open jobs for that day from the main job table and storing the daily results in a new table. (Dynamically, with the help of PHP of course) Than, I could connect Tableau to the new table and lay out the overtime job load for each user.

              • 4. Re: Using a date parameter as a column?
                David Rowe

                Hi Trent,

                 

                I have been working on the same problem with a table like:

                |Case ID|Open date|Close date|Department|

                Which seems very similar to your data.

                 

                A better solution from Joe Mako that I found on the forums is to use custom SQL like described in this thread: Trending Point in Time Data

                 

                Joe's solution transforms the data to:

                |Case ID|Event date|Event type (open/close)|Department

                 

                This will double the number of rows and each case will have a row for its open and close date.

                Then you can use a few calculated fields and running totals to show what you need.

                 

                Whether this is easier for you will depend a little on your data source, but it does make it a lot more automatic than your process.