3 Replies Latest reply on Dec 12, 2012 12:47 PM by Jonathan Drummey

# How do I compare the results for week 1 versus the aggregated results of the rest of the month - for the entire year?

I have a data where I want to compare the results of Week 1 versus the aggregated results of the rest of the month. I'd like to do for months in the year.

I've attached an example of what I'd like - except I'd like to do [Week 1 VS Weeks_of_Rest_of_Month]

• ###### 1. Re: How do I compare the results for week 1 versus the aggregated results of the rest of the month - for the entire year?

You didn't specify the aggregation you wanted for the rest of the month, I set an example up using average in the attached.

• ###### 2. Re: How do I compare the results for week 1 versus the aggregated results of the rest of the month - for the entire year?

Thanks for your reply. I looked at your file. Thank you.

Attached is an example of what I'm trying to do. Again, I appreciate the help.

• ###### 3. Re: How do I compare the results for week 1 versus the aggregated results of the rest of the month - for the entire year?

Hi,

See the attached. It uses a nested table calculation with an Advanced Compute Using of Week of Order Date. The inner calculation (Avg Rest of Month Sales) generates a sum of the sales for the 2nd through Nth (4th, 5th, or 6th) weeks of the month, then divides that by the # of weeks-1 to get the average of sales. That is wrapped in PREVIOUS_VALUE so that it is only calculated once per partition (per combination of Month, Year, and any other dimensions that are part of the partitioning). The outer Sales Comparison measure is based on an IF statement that checks if the week is the 1st week, if so it returns the sum of sales, otherwise it returns the Avg Rest of Month Sales.

There are a lot of variants that can be created of this, I tossed together a couple as well.

Jonathan