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

    Time axis on a number of days graph

    David Rowe



      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:








      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



      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"




          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



                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