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

Calculating measure for the current month of Previous Year Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 5:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:30 AM
Points: 3, Visits: 31
Hello,
I would like to calculate measures in MDX for the current month of the previous year.

Example : Today, we are the 20/03/2014.
My measure Nb Open Days n-1 will be the aggregation of my measure Nb Open Days still the[color=#red] 20/03/2013[/color].

I have build my calculation like this :
SUM(
Generate
({Existing [Time].[Date].Children},
{PARALLELPERIOD([Time].[Year - Month - Date].[Year],
1,
[Time].[Year - Month - Date].CURRENTMEMBER)
}
),[Measures].[Nb Jour Ouvré])

Through the browser, when I put my different attributes in the body of my query, it works.

But for the time attribute "Day_Week" which contains the day of the week (monday, tuesday...), it doesn't work.
The browser displays only "(null)" value.

I don't understand why ? Someone can help me ?
Post #1553955
Posted Tuesday, March 25, 2014 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
Hi,

Today I faced a similar situation and I started using the ParallelPeriod function, but later I found this blog entry:
Retrieve selected year data and same level data from previous year

I think it would be very helpful.

Kind Regards,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1554494
Posted Tuesday, March 25, 2014 8:42 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:30 AM
Points: 3, Visits: 31
Hello,

Thank you for your link and your help.

But, I have found this link that corresponds more to my issue :
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2f822a5-2a7a-4ec5-9db4-35a107d8fcb9/mdx-parallelperiod-month-to-month

Here is my MDX request if it can help :
SUM(Generate(Existing {[Time].[Date].Children},
{PARALLELPERIOD([Time].[Year - Month - Date].[Year]
,1
,OPENINGPERIOD([Time].[Year - Month - Date].[Date],[Time].[Year - Month - Date].CurrentMember))
:
PARALLELPERIOD([Time].[Year - Month - Date].[Year]
,1
,TAIL(DESCENDANTS([Time].[Year - Month - Date].CurrentMember,[Time].[Year - Month - Date].[Date],LEAVES)
,1).Item(0))
})
,[Measures].[Nb Jour Ouvré]
)
Post #1554515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse