How to get YTD Average Weekly Sales

Question

• Hello,

I have a time dimension that is Year->Quarter->Period->Week->Date.  My query is filtered by a specific Period (currentmember).  I need to get the average weekly sales YTD.  I am having a hard time getting the count of weeks YTD.  The query below will give me only the count for the currentmember which is 4.  Suggestions?  I feel like I've tried a million different things to no avail.  I do have a YTD calculated member time calc in my cube, which is based off of the current member.  Any help would be very much appreciated!

Thanks,

Melissa

CREATE

MEMBER CURRENTCUBE.[Measures].[Avg Weekly Sales Per Store YTD]

as

([Measures].[Sales YTD]) /

((

COUNT(Descendants([Time].[Fiscal].CurrentMember,[Time].[Fiscal].[Week]),INCLUDEEMPTY)) *

([Measures].[Account Period Store Cnt YTD]))

Monday, February 20, 2012 4:09 PM

• Well, seems like sometimes you just have to ask someone else in order for you to see the answer. :)  SHould have posted last week!  This is how I was able to get it:

COUNT

(PeriodsToDate([Time].[Fiscal].[Year], ClosingPeriod([Time].[Fiscal].[Week], [Time].[Fiscal].currentmember)))

• Marked as answer by Monday, February 20, 2012 4:31 PM
Monday, February 20, 2012 4:31 PM

All replies

• Well, seems like sometimes you just have to ask someone else in order for you to see the answer. :)  SHould have posted last week!  This is how I was able to get it:

COUNT

(PeriodsToDate([Time].[Fiscal].[Year], ClosingPeriod([Time].[Fiscal].[Week], [Time].[Fiscal].currentmember)))

• Marked as answer by Monday, February 20, 2012 4:31 PM
Monday, February 20, 2012 4:31 PM
• Perhaps someone can explain to me why the first member below works fine but the second gives me an error?  The error states " The  function expects a string or numeric expression for the argument.  A tuple set expression was used.

member

measures.YTDWeekCnt as COUNT(PeriodsToDate([Time].[Fiscal].[Year], ClosingPeriod([Time].[Fiscal].[Week], [Time].[Fiscal].currentmember)))

member

measures.YTDWeekCnt3 as COUNT(PeriodsToDate([Time].[Fiscal].[Year], Tail(Descendants([Time].[Fiscal].CurrentMember, [Time].[Fiscal].[Week]), 1)))

Monday, February 20, 2012 5:12 PM