none
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

Answers

  • 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 Melissa7913 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 Melissa7913 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