Re: calculated member with datediff and AVG

#1

you have to do following things to acheive this :-
create a location dimension
create multiple levels e.g
Level1 - LocationName
Level2 -- LocationSL
at the Level LocationSL create Member Properties (TestDate) and (OfferDate)

Then Create 2 calculated Measure like this :-

WITH member
Measures.DayBtwn AS 'IIF((Location.CurrentMember.Level IS
Location.LocationSL),
DateDiff(d,VBA!CDate(Location.CurrentMember.Proper ties("TestDate")
,VBA!CDate(Location.CurrentMember.Properties("Offe rDate") ),NULL)

WITH member
Measures.AvgDays AS 'IIF((Location.CurrentMember.Level IS
Location.LocationName),
SUM(Location.CurrentMember.Children,Measures.DayBt wn)/Count(Location.Current
Member.Children),NULL)

This will do it.
Tell me if it worked

"devnoronha" <member33654 (AT) dbforums (DOT) com> wrote

Quote:

Hi
Could anyone help me out with a calculated member problem ?

If the folowing is the Data in the SQL Server
------------------------------------
SL Location Testdate Offerdate
------------------------------------
1 K1 1/3/2003 1/6/2003
2 K1 2/3/2003 2/4/2003
3 K2 2/3/2003 2/10/2003
4 K2 3/20/2003 3/25/2003

I need to find the Average of the difference of the 2 dates for each
location (K1 and K2) ie.
avg(DATEDIFF (d,Testdate,Offerdate)) .
I know there is an AVG and Datediff function , but how do i use
them in conjunction . could you give me the exact syntax as i'm
kinda new to this.

I want the result to show
Loc AVG
----------------
K1 2 (3 days + 1 day = 4/2 = 2)
K2 6 (7 days + 5 days = 12/2 = 6)

thnks

--
Dev Noronha

Posted via http://dbforums.com

#2

hey akshay

thanks for the reply . could you check the parantheses '(' of your mdx
stmts ? what you've posted seems to be wht i'm lookin for .

i'm creating the calculated field using Edit - Calculated Member - New
Calculated Memebr so will "WITH member

Measures.DayBtwn AS" be applicable cos i'll be giving a 'Member name'
in the Member name text box ?

so wht you are trying to do is first get a set of the datediff's of
all rows belonging to K1 and K2 and then running an average .... is
that right ?

thanks for taking the trouble

--
Dev Noronha

Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: calculated member with datediff and AVG

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: calculated member with datediff and AVG - 08-17-2003 , 10:14 PM

Re: calculated member with datediff and AVG - 08-18-2003 , 02:08 PM