MDX Expression Help
I have two datetime field, startdate and enddate, and I like to calcuate the time spent between startdate and enddate, so I add name calculation call "TimeDiff" in dsv like this:
(some value in enddatetime field has no hh:mm value, that is why I am checking this in my case stmt)
case
when startdatetime > enddatetime then 0
else
datediff(mi, startdatetime, enddatetime)
end
then I create calculated memeber (AvgTime) like this:
[Measures].[Time Diff]/[Measures].[RecordCount], here is the result in OWC, which is not what I want, notice the avg is 0.1 which is sum (0.1+8.1)/12
Jan .0
Feb .0
Mar .0
Apr .0
May .0
Jun .1
Jul 8.1
Aug .0
Sep .0
Oct .0
Nov .0
Dec .0
AvgTime .8
How do I write my Expression just calculate average of non zero member like this (0.1+8.1)/ 2 ?
[918 byte] By [Dz0001] at [2008-1-2] 