July 24, 2014 at 1:23 pm
The query below produces an error and I have hard time trying to figure out why. It is something to do with aggregating Date dimension members but I don't know how to get it to work. Any kind of help is appreciated.
WITH
MEMBER [Measures].[Min AR Total Last 6 months] as '(MIN([Date].[Fiscal Month Hierarchy].currentmember.lag(6):[Date].[Fiscal Month Hierarchy].currentmember,[Measures].[AR Total]))'
MEMBER [Date].[Fiscal Month Hierarchy].[FilterAggregate] as
'AGGREGATE({
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201310],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201311],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201312],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201401],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201402],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201403],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201404],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201405],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201406],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20144].&[201407]
})'
SELECT {
[Measures].[Min AR Total Last 6 months]} ON AXIS(0)
FROM [My Cube]
WHERE ([Date].[Fiscal Month Hierarchy].[FilterAggregate])
July 25, 2014 at 1:29 pm
What is the output you are expecting?
July 25, 2014 at 8:06 pm
I am expecting this query to return a numeric value - the lowest [AR Total] value in the last 6 months (rolling minimum), for the selected date hierarchy members.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply