6 Replies Latest reply on Jan 7, 2013 6:05 PM by Alex Kerin

    Repeat a value based on date comparison

    Kay Ross

      I am attempting to create a chart that will repeat the cpu used for each day the job was running. The table used contains the fields; jobid, startdate, enddate and cpuused.  If the startdate does not equal the enddate, I would like to display the cpuused. Is there a way to do this in Tableau?  Thank you for your help!

       

      Example actual table data:

       

      jobid startdate enddate cpuused
      112/1/201212/3/201226
      212/2/201212/2/20121
      312/5/201212/5/21025

       

      Additional records were created in my test file to show how I would like Tableau to display the data. This is not how the data actually exists in the table I'm connecting to. 

       

      jobid startdate enddate cpuused
      112/1/201212/3/201226
      112/2/201212/3/201226
      112/3/201212/3/201226
      212/2/201212/2/20121
      312/5/201212/5/21025

       

      Below would be the data point's displayed in the chart.  I have attached an example of the test Tableau report created.

      12/1/2012 - 26 CPU Used

      12/2/2012 - 27 CPU Used

      12/3/2012 - 26 CPU Used

      12/5/2012 - 5 CPU Used

       

       

      Attached is an example of my test Tableau Report:

        • 1. Re: Repeat a value based on date comparison
          Alex Kerin

          This is a classic utilization problem. I have a post about it here: http://www.datadrivenconsulting.com/2012/10/queues-and-utilization-in-tableau-part-1-queues/. This is based on the excellent help I have received on this forum.

           

          Yours is a little different - I have a question - wouldn't 12/2 have 52 CPU cycles or does the CPU usage end at midnight?


          Edit: I guess your results are based on the first table?

          • 2. Re: Repeat a value based on date comparison
            Alex Kerin

            Here are a few possibilities attached.

             

            I have used the data from your second table, then duplicated the source three times to show different results with different custom SQL. The first uses the approach on my blog but with no steps created ( the lack of steps makes it a little hard to discern daily usage). The issue with this is that the CPU usage starts and stops at midnight, so days like the 5th actually show no usage for the day (we are using a running sum as part of the chart)

             

            The second SQL statement adds 24 hours to the end date, representing the worst case - that the CPU cycles ran all 24 hours. Now the 5th has a value, but without the steps its still difficult to discern what's going on.

             

            Finally the last SQL has added marks where no CPU cycles are added or taken away, but allow us to create steps. Joe Mako has a neater way of creating steps (http://community.tableau.com/message/135833#135833), but I couldn't get that to work with the utilization setup we have here. util.png

            • 3. Re: Repeat a value based on date comparison
              donna trinh

              you can create a calculated field for if [start date] != [end date] then [CPUused] else null (not sure if you can have the CPUused in the past vs. now in the same table to display the same CPU being used) end

              • 4. Re: Repeat a value based on date comparison
                Alex Kerin

                That won't work in this instance.

                 

                Edit: let me explain why. If you had tried your equation, you'd have seen that against the given data it doesn't give the right results. That's because the row level data can only ever represent the dates you have information for. If the CPU cycles start on the 1st and end on the 5th, in the original data format there are no rows for the 2nd, 3rd, or 4th. Therefore Tableau could never know about the CPU cycles on these dates. We need to do one of two things, rearrange the data so we can increment and decrement the CPU cycles (as I have done), or pad the data out so that we have information for every single day (creating time buckets). This is also done with custom SQL.

                • 5. Re: Repeat a value based on date comparison
                  Kay Ross

                  Hi Alex

                   

                  Regarding your question:  Yours is a little different - I have a question - wouldn't 12/2 have 52 CPU cycles or does the CPU usage end at midnight?

                   

                  No. For the data example below, I would like a data point of 26 for 12/1/2012, 12/2/2012 and 12/3/2012. 

                   

                  jobid startdateenddate cpuused
                  112/1/201212/3/201226

                   

                  Thank you for your help!

                  • 6. Re: Repeat a value based on date comparison
                    Alex Kerin

                    Then that is what my solution provides I believe.