Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.
From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.

Median

How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.From Northwind:
select
Median = avg (Quantity)
from
(
select
Quantity = min (Quantity) -- first value ...
from
(
select top 50 percent -- ... in upper half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity desc
) x
union all
select
Quantity = max (Quantity) -- last value ...
from
(
select top 50 percent -- ... in lower half
Quantity = sum (Quantity)
from
[Order Details]
group by
OrderID
order by
Quantity asc
) x
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O1vtO0OrFHA.1984@.tk2msftngp13.phx.gbl...
How do I find the median value for a column? What I want to do is find the
median sales price of a particular item from my sales history.sql

MDX Question on getting the top record for each....

I have scenario where a Item/Store combination can appear multiple times for the same period and to get the most recent record within a range ([Dim ZeroSalesDays].[Zero Sales Days]), I need to return the most current record. So I created measure with aggregation usage as last non empty.

So the below query works fine in that scenario.

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],(Aggregation usage – sum)

[Measures].[Last Tagged],[Measures].[Last Void](Aggregation usage – Last Non Empty)

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children},

{[Dim Store].[StoreName].Children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[30]}, {[Dim Period].[Period].[200702]}

)

Now they want to return [Dim ZeroSalesDays].[Zero Sales Days] value which was selected. How do I return that in the rows or columns. I can do order by and get the top record, but that will work only if I select a particular store and item, not for the entire set of all items/products.

with set

[Elapseddays] as

'order(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]},

cint([Dim ZeroSalesDays].[Zero Sales Days].value), desc)

'

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void]

} ON COLUMNS,

topcount(

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].&[4-DELMO -00090A]},

{[Dim Store].[StoreName].&[11733]},

{[Elapseddays]}

)

, 1)

ON ROWS

FROM sifdw

Any kind of help is appreciated.

Thank you in advance

Thank you,

Manish

Hi Manish,

Each row can include the last date of each individual Product/Store combination, like:

Code Snippet

SELECT

{[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void] } ON COLUMNS,

Generate(([Dim Product].[Item Id].Children, [Dim Store].[StoreName].Children),

Tail(NONEMPTY(([Dim Product].[Item Id].CurrentMember, [Dim Store].[StoreName].CurrentMember,

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]}),

([Dim Period].[Period].[200702], [Measures].[Tagged])))) ON ROWS

FROM sifdw

|||

Hi Deepak,

Somehow I was not able to get the query run successfully.

Let me attach the query what I have

SELECT

{

[Measures].[Last on Shelf]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children}

,{[Dim Store].[Store Id].children}

,{[Dim Date].[Date].children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[25]},

{[Dim ZeroSales].[Zero Sales].&[61]},

{[Dim ZeroSales].[Zero Sales Period].&[402006]},

{[Dim ZeroSales].[Team ID].&[4]}

)

Now in this given scenario, for each item / store combination, there can be multiple date. I want the most recent date for Item/store combination (Rows should display 3 columns - Item, Store and Date).

Thank you,

Manish

|||

Hi Manish,

Could you explain the realtion between [Dim ZeroSalesDays] and [Dim Date] - the latter wasn't mentioned in your original description? And along which dimension is the most recent value (LastNonEmpty) found - since your original question related to determining the member along that dimension?

|||

Hi Deepak,

Sorry for the confusion.

[Dim ZeroSalesDays] and [Dim Date] are 2 dimension.

In the fact table, lets say we have these 6 colums. Item, Store, Date, ZerosalesDays and period are dimension and on shelf is a measure.

Now in a query, for a period '5', between 1 & 45 zero sales days, I wan't the most recent record for that Item/Store combination along with the date. (It should return me 2nd record, if I change the zero sales days between 1 and 25 then it should return me 1st record and so on)

Item Store Date On Shelf ZeroSalesDays Period

00016 0800209 11/02/2006 2 24 5
00016 0800209 11/15/2006 5 37 5

00016 0800209 11/25/2006 2 47 5

I hope I made it clear. Let me know if you need any other information.

I am trying to use Generate function... but it's just spinning for ever.. May be I am missing something.

Thank you,

Manish

MDX Question on getting the top record for each....

I have scenario where a Item/Store combination can appear multiple times for the same period and to get the most recent record within a range ([Dim ZeroSalesDays].[Zero Sales Days]), I need to return the most current record. So I created measure with aggregation usage as last non empty.

So the below query works fine in that scenario.

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],(Aggregation usage – sum)

[Measures].[Last Tagged],[Measures].[Last Void](Aggregation usage – Last Non Empty)

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children},

{[Dim Store].[StoreName].Children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[30]}, {[Dim Period].[Period].[200702]}

)

Now they want to return [Dim ZeroSalesDays].[Zero Sales Days] value which was selected. How do I return that in the rows or columns. I can do order by and get the top record, but that will work only if I select a particular store and item, not for the entire set of all items/products.

with set

[Elapseddays] as

'order(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]},

cint([Dim ZeroSalesDays].[Zero Sales Days].value), desc)

'

SELECT

{

[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void]

} ON COLUMNS,

topcount(

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].&[4-DELMO -00090A]},

{[Dim Store].[StoreName].&[11733]},

{[Elapseddays]}

)

, 1)

ON ROWS

FROM sifdw

Any kind of help is appreciated.

Thank you in advance

Thank you,

Manish

Hi Manish,

Each row can include the last date of each individual Product/Store combination, like:

Code Snippet

SELECT

{[Measures].[Tagged], [Measures].[Void],[Measures].[On Shelf],

[Measures].[Last Tagged],[Measures].[Last Void] } ON COLUMNS,

Generate(([Dim Product].[Item Id].Children, [Dim Store].[StoreName].Children),

Tail(NONEMPTY(([Dim Product].[Item Id].CurrentMember, [Dim Store].[StoreName].CurrentMember,

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[45]}),

([Dim Period].[Period].[200702], [Measures].[Tagged])))) ON ROWS

FROM sifdw

|||

Hi Deepak,

Somehow I was not able to get the query run successfully.

Let me attach the query what I have

SELECT

{

[Measures].[Last on Shelf]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

{[Dim Product].[Item Id].Children}

,{[Dim Store].[Store Id].children}

,{[Dim Date].[Date].children}

)

ON ROWS

FROM sifdw

WHERE

(

{[Dim ZeroSalesDays].[Zero Sales Days].&[1] : [Dim ZeroSalesDays].[Zero Sales Days].&[25]},

{[Dim ZeroSales].[Zero Sales].&[61]},

{[Dim ZeroSales].[Zero Sales Period].&[402006]},

{[Dim ZeroSales].[Team ID].&[4]}

)

Now in this given scenario, for each item / store combination, there can be multiple date. I want the most recent date for Item/store combination (Rows should display 3 columns - Item, Store and Date).

Thank you,

Manish

|||

Hi Manish,

Could you explain the realtion between [Dim ZeroSalesDays] and [Dim Date] - the latter wasn't mentioned in your original description? And along which dimension is the most recent value (LastNonEmpty) found - since your original question related to determining the member along that dimension?

|||

Hi Deepak,

Sorry for the confusion.

[Dim ZeroSalesDays] and [Dim Date] are 2 dimension.

In the fact table, lets say we have these 6 colums. Item, Store, Date, ZerosalesDays and period are dimension and on shelf is a measure.

Now in a query, for a period '5', between 1 & 45 zero sales days, I wan't the most recent record for that Item/Store combination along with the date. (It should return me 2nd record, if I change the zero sales days between 1 and 25 then it should return me 1st record and so on)

Item Store Date On Shelf ZeroSalesDays Period

00016 0800209 11/02/2006 2 24 5
00016 0800209 11/15/2006 5 37 5

00016 0800209 11/25/2006 2 47 5

I hope I made it clear. Let me know if you need any other information.

I am trying to use Generate function... but it's just spinning for ever.. May be I am missing something.

Thank you,

Manish

MDX Help.

This is my current MDX for a KPI in Performance Point:

(([Current Month].item(0), [Measures].[Revenue]) - ([Current Month].item(0).PrevMember, [Measures].[Revenue]))/ ([Current Month].item(0).PrevMember, [Measures].[Revenue])

I need to modify this to only include Customers of a of Type Dealer and Distributor in my cube.

[Customers].[Customers].[Customer Type].&[Dealer]

[Customers].[Customers].[Customer Type].&[Distributor]

[Customers].[Customers].[Customer Type].&[Direct] (do not include)

Wow...it seems like it should be so easy...but I cannot get the syntax.

Help!!!!

Linda

Hi Linda,

You could create a calculated member to agrgegate the 2 included customer types, like:

Create Member CurrentCube.[Customers].[Customers].[Dealer-Distributor] as

Aggregate({[Customers].[Customers].[Customer Type].&[Dealer],

[Customers].[Customers].[Customer Type].&[Distributor]} )

and another to compute Revenue Growth %, like:

Create Member CurrentCube.[Measures].[RevenueGrowth%] as

([Measures].[Revenue] - ([Date].[Month].PrevMember, [Measures].[Revenue]))

/ ([Date].[Month].PrevMember, [Measures].[Revenue])

Then the KPI could be:

([Current Month].item(0), [Customers].[Customers].[Dealer-Distributor], [Measures].[RevenueGrowth%])