SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


mdx - MIN and MAX not in context with SUBLECT


mdx - MIN and MAX not in context with SUBLECT

Author
Message
robinrai3
robinrai3
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 365
Hi All,

My journey into mdx is always one step forward and three back (a story for another day...). Using adventure works i have my own user defined CalendarHierarchy on year,month and day

todays puzzle :

1st query ...taking the min and max values over a year range ...results as expected..min and max occurs at 2005 and 2007 respectivly


WITH MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'

SELECT
{[Measures].[Sales Amount] ,[Measures].[MinValue],[Measures].[MaxValue]} ON 0,
[Order Date].[CalendarHierarchy].[Year].Members ON 1

FROM
MyFirstCube


Year Sales Amount MinValue MaxValue
2005 3266373 3266374 9791060
2006 6530343 3266374 9791060
2007 9791060 3266374 9791060
2008 9770899 3266374 9791060




however when doing a subselect on year 2006 which is a sub cube on 2006 I get

WITH MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'

SELECT
{[Measures].[Sales Amount],[Measures].[MinValue],[Measures].[MaxValue]} ON 0,
[Order Date].[CalendarHierarchy].[Year].Members ON 1
FROM
(
SELECT [Order Date].[CalendarHierarchy].[Year].[2006] ON 0
FROM MyFirstCube
)

Year Sales Amount MinValue MaxValue
2006 6530343 3266374 9791060


NOTE the Min and Max values are still from 2005 and 2007 ...why ? shouldn't it be 6530343 for min and max...isn't the data completely filtered out for 2006 now ...doh


The following shows i only have the 2006 year in left my member list


SELECT [Order Date].[CalendarHierarchy].[Year].Members ON 0
FROM
(
SELECT[Order Date].[CalendarHierarchy].[Year].[2006] ON 0
FROM MyFirstCube
)

Year Sales Amount
2006 6530343


rgds all


robin
PB_BI
PB_BI
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 2519
Hi Robin,
I think everyone's MDX journey is a little like that :-D

The problem you are having is because what you are asking MDX to display is the minimum/maximum of all year members in the hierarchy, not just the member that is displaying. So your second query is technically correct. It's saying in 2006 the sales amount was 6530343 but the minimum amount looking at all the year members at the year level is 3266374 and the maximum amount looking at all the year members at the year level is 9791060.

It all depends on what you are trying to achieve. For example let's say a calendar hierarchy is Year>Quarter>Month>Week>Day. You could do something like

WITH MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].CURRENTMEMBER.CHILDREN, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].[Year].CURRENTMEMBER.CHILDREN, [Measures].[Sales Amount]), FORMAT_STRING = '0'

SELECT
{[Measures].[Sales Amount],[Measures].[MinValue],[Measures].[MaxValue]} ON 0,
[Order Date].[CalendarHierarchy].[Year].Members ON 1
FROM
(
SELECT [Order Date].[CalendarHierarchy].[Year].[2006] ON 0
FROM MyFirstCube
)



This would give you the sales amount for the lowest and highest performing Quarters in 2006.

Like I say though, it all depends on what you are trying to get out of the query.

I hope this clarifies things a bit :-)




I'm on LinkedIn
robinrai3
robinrai3
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 365
Hi SSC Veteran,

This kiddie works :

WITH MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].CURRENTMEMBER, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].CURRENTMEMBER, [Measures].[Sales Amount]), FORMAT_STRING = '0'

SELECT
{[Measures].[Sales Amount],[Measures].[MinValue],[Measures].[MaxValue]} ON 0,
[Order Date].[CalendarHierarchy].[Year].Members ON 1
FROM
(
SELECT [Order Date].[CalendarHierarchy].[Year].[2006] ON 0
FROM MyFirstCube
)


Year Sales Amount MinValue MaxValue
2006 6530343 6530343.52639994 6530344 6530344


but i'm confused ...using the currentmember seems to force it to use the context of the subcube. However my question is we have used a SUBSELECT where we have created a subcube purely of only 2006 figures Sales and the [All] which is the root of the calendar hierarchy has only this year in its member list. This is not a WHERE we it acts as a slicer where i would still expect it to see the other years.

So using the currentmember or the literal viz:

MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount]), FORMAT_STRING = '0'

should in my eyes give the same thing. Is my argument wrong ?
PB_BI
PB_BI
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 2519
Hi Robin,
Actually I don't think that is working.

What your result should be is the same figure 3 times. It would if you wrote the query like this:

WITH MEMBER [Measures].[MinValue] AS MIN([Order Date].[CalendarHierarchy].CURRENTMEMBER, [Measures].[Sales Amount]), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX([Order Date].[CalendarHierarchy].CURRENTMEMBER, [Measures].[Sales Amount]), FORMAT_STRING = '0'

SELECT
{[Measures].[Sales Amount],[Measures].[MinValue],[Measures].[MaxValue]} ON 0,
[Order Date].[CalendarHierarchy].[Year].&[2006] ON 1
FROM
MyFirstCube



Because you are not specifying a measure in your sub cube, it is taking the default measure of your cube (typically the first measure in the first measure group in your designer) which in your case may not be [Sales Amount].

To specify the min and the max of the current member is sort of meaningless unless you specify a level.

For example the code on this page returns the maximum monthly sales for each quarter, subcategory, and country in the Adventure Works cube:

WITH MEMBER Measures.x AS Max
([Date].[Calendar].CurrentMember.Children
, [Measures].[Reseller Order Quantity]
)
SELECT Measures.x ON 0
,NON EMPTY [Date].[Calendar].[Calendar Quarter]*
[Product].[Product Categories].[Subcategory].members *
[Geography].[Geography].[Country].Members
ON 1
FROM [Adventure Works]



It's using .currentmember.children so it is looking at the children of [Date].[Calendar].[Calendar Quarter] which are months so it's meaningful (it displays the total sales for the most successful month). Without the .children it would just display a total for the quarter in which case there would be no point in having a calculation at all.

I hope this helps :-)




I'm on LinkedIn
robinrai3
robinrai3
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 365
hi yayomayn

I did some more investigative trial and error stuff and have now got it to work with the subselect. In the subselect the default measure is "internet sales" so need to specify it. What I did was to put an extra member of YearCount. Again in my opinion since the subselect acts a complete "guillotine" it should have only have one year in it. Which when I display it shows the one year 2006. However the count says 4. This got me scratching my head...its only when I wrapped the "existing" function around the count did it come out as 1. Then the penny dropped the existing is forcing the the members in context of the subcube. The mdx is now as follows


WITH
MEMBER [Measures].[MinValue] AS MIN(Existing([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount])), FORMAT_STRING = '0'
MEMBER [Measures].[MaxValue] AS MAX(Existing([Order Date].[CalendarHierarchy].[Year].Members, [Measures].[Sales Amount])), FORMAT_STRING = '0'
MEMBER [Measures].[YearCount] AS Count(Existing([Order Date].[CalendarHierarchy].[Year].Members))
SELECT
{[Measures].[Sales Amount],[Measures].[MinValue],[Measures].[MaxValue], [Measures].[YearCount]} ON 0,
[Order Date].[CalendarHierarchy].[Year].Members ON 1
FROM
(
SELECT
[Order Date].[Calendar Year].&[2006] ON 0
FROM MyFirstCube
)

and the results are :

Year Sales Amount MinValue MaxValue YearCount
2006 6530343 6530344 6530344 1



without the "existing" function wrapper around the members we have

Year Sales Amount MinValue MaxValue YearCount
2006 6530343 3266374 9791060 4

food for thought ...My opinion is still the existing should not have to be used...but there you go..any way been a pleasure to discuss it with you my friend
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search