October 28, 2013 at 5:16 am
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
2005326637332663749791060
2006653034332663749791060
2007979106032663749791060
2008977089932663749791060
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
)
YearSales Amount MinValue MaxValue
2006653034332663749791060
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
October 28, 2013 at 6:01 am
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 🙂
October 28, 2013 at 9:27 am
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 AmountMinValue MaxValue
2006 6530343 6530343.5263999465303446530344
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 ?
October 28, 2013 at 10:33 am
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 🙂
October 29, 2013 at 5:14 am
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 :
YearSales AmountMinValue MaxValue YearCount
20066530343 6530344 6530344 1
without the "existing" function wrapper around the members we have
YearSales AmountMinValue MaxValue YearCount
20066530343 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply