Median Calculation - Analysis Services

A really simple question,,,, I need to calculate the median of a
measure. I'm having a tough time to figure out which dimension to use.
If If I have 4 dimensions, Date, region, site, and process code and I
want to get the median for the measure number of days, what do I use to
define the set?
median ([Process].AllMembers,measures.[No Of Days]) this is not working,
but at least the syntax was clean
standard median prompt
Median (Set[, Numeric Expression])
As I select different subsets of region, site and process code I
obviously want the median of No of days to be dynamic.
Stuck here and waiting anxiously for some bright person to shed the
light!!!
thanks,,,
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I would try the sqlserver.olap group with this one. This is more the
relational engine area.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Peter Weiler" <pweiler@.rogers.com> wrote in message
news:eLlFlRDIFHA.572@.tk2msftngp13.phx.gbl...
> A really simple question,,,, I need to calculate the median of a
> measure. I'm having a tough time to figure out which dimension to use.
> If If I have 4 dimensions, Date, region, site, and process code and I
> want to get the median for the measure number of days, what do I use to
> define the set?
> median ([Process].AllMembers,measures.[No Of Days]) this is not working,
> but at least the syntax was clean
> standard median prompt
> Median (Set[, Numeric Expression])
> As I select different subsets of region, site and process code I
> obviously want the median of No of days to be dynamic.
> Stuck here and waiting anxiously for some bright person to shed the
> light!!!
> thanks,,,
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

MDX to Determine Week Ending Date

Hello,

I'm trying to write MDX that will return the last child for the week having a full 7 days of data. I'm close, but can't figure out how to get the date to return. The sample below returns the correct week, but whenever I use "lastchild", etc. I get that lastchild expects a member and a tuple is being used.

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[18]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[26]}, measures.X.Value = 7 )) on 0,

nullon 1

From

[MyCube]

This is to be used in a report header, so ultimately I'd like it to return a string like "Data as of Week Ending 6/16/2007"

Any help is appreciated.

So, this doesn't answer the question, but could you approach the overall problem another way to get what you're looking for? For example, you could have an attribute in your date dimension that recorded the day of the week number from 1 to 7. If you assume that if there exists data for the 7th day, the week is completed. If this gives you what you're looking for, you could determine the last day 7 associated with fact data with a query like this:

Code Snippet

select

{} on 0,

TAIL(

HIERARCHIZE(

EXISTS(

[Delivery Date].[Date].Members,

[Delivery Date].[Day of Week].&[7],

'Internet Sales'

)

),

1

) on 1

from [Adventure Works]

|||

I was actually able to get around the issue by using .item(0). See the working code below:

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

nullon 0,

Tail(Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[1]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[53]}, measures.X.Value = 7 )).Item(0).Children) on 1

From

[MyCube]

This code returns the date of the last day of the last full week of data.

MDX question

I'm new to MDX and I'm trying figure out how to do the following:

I have a fact table that represents magazine subscriptions. for each subscription, I need to determine if there is a renewal subscription available. I have a hierarchy which consists of MasterSubID, IndividualSubID and TermNumber. MasterSubID represents the lifetime of subscriptions for an individual customer. IndSubID represents each subscription for a customer. TermNumber increments each time a customer orders a subscription, so the first time they subscribe, termnumber is 1, if they renew it would be 2 and so on.

Now, each subscription has an expiredate. For each expire date, I need to find all subscriptions that are expiring on that date. I then need to determine if there is another subscription with the same MasterSubID and a termnumber that is after the current term number. This would be the renewal. I would then need to calculate the percentage renewed by expire date.

The biggest problem I have right now is determining if there is a subscription after the current one.I tried using various functions: nextmember, lead(1), etc. but I don't seem to be getting anywhere.

Any help would be greatly appreciated.

Frank

Thanks.

I think you need to do that in the datatable before you populate the cube, and then create a attribute with the value renewed = true.

MDX question

I'm new to MDX and I'm trying figure out how to do the following:

I have a fact table that represents magazine subscriptions. for each subscription, I need to determine if there is a renewal subscription available. I have a hierarchy which consists of MasterSubID, IndividualSubID and TermNumber. MasterSubID represents the lifetime of subscriptions for an individual customer. IndSubID represents each subscription for a customer. TermNumber increments each time a customer orders a subscription, so the first time they subscribe, termnumber is 1, if they renew it would be 2 and so on.

Now, each subscription has an expiredate. For each expire date, I need to find all subscriptions that are expiring on that date. I then need to determine if there is another subscription with the same MasterSubID and a termnumber that is after the current term number. This would be the renewal. I would then need to calculate the percentage renewed by expire date.

The biggest problem I have right now is determining if there is a subscription after the current one.I tried using various functions: nextmember, lead(1), etc. but I don't seem to be getting anywhere.

Any help would be greatly appreciated.

Frank

Thanks.

I think you need to do that in the datatable before you populate the cube, and then create a attribute with the value renewed = true.