I’ll need to create a universe measure which will calculate time different between 2 field - start date and end date.
The report that will using this universe measure will need to show the result in “xx days, x hours” kind of format, for example: “1 day 2 hours”.
Also, the measure need to be able to get aggregate, for example, average run time of West region project.
I search on the forum and found ppl suggesting using seconds in the measure so it can be aggregated, but at the end how do we format it in the xx day yy hour format in the Webi or cyrstal report?
Do I need to create custom function to format it?
Thanks for your information, I tried it out by adding a measure “duration in seconds” and then another measure “duration in day and hour” with the format function to refer to the “duration in seconds” and the result does not behave like what I expected.
For example I have these few rows in the table:
Department TaskName Start End
Support Task1 1/22/2008 3:00pm 1/22/2008 4:30pm
Support Task2 1/22/2008 3:00pm 1/22/2008 4:30pm
Finance Task3 1/23/2008 12:00am 1/25/2008 12:30am
Finance Task4 1/23/2008 12:00am 1/25/2008 12:30am
When I group them by department, the result become:
Department Duration in Seconds Duration in hour and day
Support 10800 0day1hr30min 0day1hr30min
Finance 349200 2day0hr30min 2day0hr30min
Basically the “duration in seconds” is aggregated but not the “duration in day and hour”.
Is that mean I should not add the measure in universe?
If I have to do it on the format in Webi, how do I do that? (sorry… I’m new to Webi)
(BOB member since 2003-11-27)