Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

mdx - MIN and MAX not in context with SUBLECT Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 5:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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

Post #1508810
Posted Monday, October 28, 2013 6:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
Hi Robin,
I think everyone's MDX journey is a little like that

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
Post #1508823
Posted Monday, October 28, 2013 9:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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 ?

Post #1508966
Posted Monday, October 28, 2013 10:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1509028
Posted Tuesday, October 29, 2013 5:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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




Post #1509277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse