Question

Shanu_Shri on Wed, 29 Mar 2017 09:26:35


My WTD calculation in MDX query for first week of every months resets to the 1 day of month instead of starting from Monday.

I think this is due the wrong hierarchy i have created in Date Dimension. I am not sure what should be the right hierarchy for a week. the current hierarchy in my Cube ias as given below.

Please suggest where should make correction to calculate the WTD correctly ( WTD MDX calcualation for first week of every month should not start from the 1st date of the month but start from the Monday in the same week)




Regards, Shanu


Sponsored



Replies

willson yuan on Wed, 29 Mar 2017 09:55:30


Hi Shanu,

Thanks for your question.

Please do not create a user hierarchy with Calendar Year---Quarter---Month---week--Date.

You may consider to create a user hierarchy called Calendar Weeks with Calendar Year---Calendar Week---Date.


For more detail, please refer to user hierarchy Calendar Weeks in Adventure Works.

Shanu_Shri on Thu, 30 Mar 2017 04:47:07


Hi , 

I think connecting week to year in a hierarchy will not solve the problem. As a week may be a part of 1 to 2 month so in a same way 1 week may be the part of two years (Dec  last to January first) So in this case the Week will be reset on every 1st January to be the first day of the week. 

Please suggest.


Regards, Shanu


willson yuan on Thu, 30 Mar 2017 07:51:28


Hi Shanu,

Thanks for your response.

As we can see from following 2 pictures, Week number does not relate to Month directly, Weeks and Month have no directly relationships, they are connected by Date. Week number of year is related to years,the start week day(in Adventure Works, a week start from sunday) and Date directly. Because of this, WTD calculation will only be calculated based on Year,Week number of year and Date,we can not create WTD calculation based on Week and Month.That's why i suggest you to create user hierarchy Calendar Weeks with Calendar Year---Calendar Week---Date. After creating this user hierarchy Calendar Weeks, you will get the right WTD value based on it.

Let's go back to your requirement, week number of year may be a part of 1 to 2 month,but week number of year can not be part of 2 years. after creating user hierarchy Calendar Weeks, to get the right WTD calculation,we may also need to use exist function to get all the week days's date.
See my sample MDX query below:

With Member [Measures].[WTD Sales Amount] As
IIF([Date].[Calendar].currentmember.level IS [Date].[Calendar].[Date],
SUM({PeriodsToDate([Date].[Calendar Weeks].[Calendar Week],
(exists([Date].[Calendar Weeks].[Date],[Date].[Calendar].currentmember)).item(0))},
[Measures].[Sales Amount]),
[Measures].[Sales Amount])
Select {[Measures].[Sales Amount],
[Measures].[WTD Sales Amount]} on 0,
[Date].[Calendar].members on 1
From [Adventure Works]
where [Date].[Calendar Year].&[2011];
With Member [Measures].[WTD Sales Amount] As
SUM(PeriodsToDate([Date].[Calendar Weeks].[Calendar Week],[Date].[Calendar Weeks].CurrentMember),
[Measures].[Sales Amount])
Select {[Measures].[Sales Amount],
[Measures].[WTD Sales Amount]} on 0,
[Date].[Calendar Weeks].members on 1
From [Adventure Works]
where [Date].[Calendar Year].&[2011];

For more detail, you can also refer to "How to calculate YTD, QTD, MTD and WTD?"  .


Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com





Shanu_Shri on Thu, 30 Mar 2017 10:57:26


Thanks a lot for your detailed response... its very helpful to me  :)