locked
Concatenate multiple weeks into one RRS feed

  • Question

  • In my quest to solve a problem with an average calculation I've made progress, but got stuck again. This probably mainly has to do with my lack of knowledge of MDX, so after searching the net, these forums, and trying numerous options myself, I turn to you guys.

    I am trying to calculate a 3 year average on Sales, filtered by stores that had sales over all 3 years, and calculated to a week-weight. So the average week is compared to the average yearly sales, thus returning a week-weight (not sure how to call it really). This avergae needs to be based on weeks 1 to 52 only, so I am trying to filter out all weeks 53.

    Part of my query is the following:
    member Measures.Test as
    	avg(
    		filter(
    			{ [Vestiging].[Vestiging Nummer].Members - [Vestiging].[Vestiging Nummer].[All] }
    			, ([Measures].[IncompletePeriodsYearInd] = 0)
    		)
    
    		,avg(
    			{
    				head(ancestor([Datum Verkoop].[Date YWD].CurrentMember, [Datum Verkoop].[Date YWD].[Year]).Lag(1).Children, 52),
    				head(ancestor([Datum Verkoop].[Date YWD].CurrentMember, [Datum Verkoop].[Date YWD].[Year]).Lag(2).Children, 52),
    				head(ancestor([Datum Verkoop].[Date YWD].CurrentMember, [Datum Verkoop].[Date YWD].[Year]).Lag(3).Children, 52)
    			}
    			, Measures.Omzet
    			
    		)
    	)
    This part needs to calculate a yearly total average, but since I get the first 52 weeks, from all 3 years, I get a weekly average. I would like to calculate the average as if it where on the 52 week subset as a whole.

    Is such a thing possible? I suspect it should be, but I'm completely lost!

    Thanks for your help in advance!
    Tuesday, January 12, 2010 9:41 AM

Answers

  • Okay, I solved this with a dirty solution by just multiplying the innermost average result by 52. Since it calculates the same week average for all weeks, and I know that due to the head() function I always averaged over 52 weeks, I can safely multiply by 52.

    Sorts the issue, but I still dont have a good feeling about this one. If anyone cares, I would still like to know how to concatenate the weeks into a single unit.
    • Marked as answer by tss68nl Tuesday, January 12, 2010 10:18 AM
    Tuesday, January 12, 2010 10:18 AM