Could you use a calculation to convert your # of days into months?
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.
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
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?
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