4 Replies Latest reply on Oct 16, 2013 4:24 PM by David Rowe

    Time axis on a number of days graph

    David Rowe


      Hi,

       

      I am working on a viz that looks at the average number of days taken across a number of tasks.

       

      The data contains the time taken to complete the task, computed as [date/time finish] - [date/time start]

       

      The viz shows the average number of days across a few task types, for example:

      36

      58

      106

      342

      380

      800

       

      The Y axis of the graph plots these as a number (obviously), however I thought that the viz would be a lot more user friendly if the Y axis looked at date ranges, for example, the current Y axis on the left would be more intuitive if it was closer to the example on the right:

       

      default Y axis          What I want

      100                    3 months

      200                    6 months

      300                    9 months

      400                    1 year

      ect.

       

      Is there any way to show the Y axis as a time scale, even though my data is simply a number of days?

        • 1. Re: Time axis on a number of days graph
          Matt Lutton

          Could you use a calculation to convert your # of days into months?

           

          Something like:

           

          IF sum(NumDays) <= 90 then "3 months"

          elseif sum(NumDays) <=120 then "6 months"

          ....

          end

           

          Of course, I am using 30 day months, but something like this may work--I'm just thinking aloud here.    


          • 2. Re: Time axis on a number of days graph
            David Rowe

            Hi Matthew,

             

            That could be a good work around.

             

            The way you are describing puts them into bins, which would be great for a histogram.

            To simply change the axis I think the calculation would be easier such as:

             

            (number of months) = (number of days)/30

             

            Then use avg.(number of months) as the measure.

            The only drawback  is that a value of 17 months would be easier to most users is displayed as "1 year, 5 months"

             

            However, I still think there is something obvious we are missing

             

            Thanks for the help

            • 3. Re: Time axis on a number of days graph
              Kyle Pipkins

              Could you calculate it as a DATEDIFF('month',[date/time finish], [date/time start]) and then format the axis? Or are you actually importing the difference and don't have the Finish and Start times?

               

              Or, I just helped someone else with this using a String and converting it to a year but how about:

               

              Originally: DATE("1/1/"+str(RIGHT([Fiscal Year],4))) - changes a string of just 2009, 2010 to be the Date field year of itself.

               

              Converted for you: DATETIME("1/1/1111:11:11:"+str(RIGHT([TimeDifference,3)))

               

              What do you think?

              • 4. Re: Time axis on a number of days graph
                David Rowe

                This is an example of the data converted to number of months using datediff

                 

                example.PNG.png

                I think this is the best we will be able to get as i need to use averaging etc. for the measures which cant be done if its stored as a date/time value.

                 

                It is much better than looking at the number of days. I think its now a pretty simple viz after changing the axis marks to every three months.

                 

                Thanks for the help guys