3 Replies Latest reply on Jun 10, 2016 7:52 AM by Joshua Milligan

# Help with LoD + Aggregates in IF statements

Hi all,

I have a complicated dataset I'm trying to approach. It has the following quirks:

+ All week dates are the Monday of each week.

+ Most products are released on Sunday (with the w/c the Monday following that Sunday their official "Release week"). So a product released on Sun 3/1/16 will have a release week of Mon 4/1/16.

+ Many products have more than 1 release date, though we are only interested in the first.

+ Some products have sales before their week of release which are technically release week sales. So the product released on 3/1/16 will have sales from w/c 28/12/15 which need to be counted.

What I'm trying to find is - for a list of about 3,000 products - the combined sales value for the week of their first release date (+ any accidental sales from the week before).

I've attached a sample workbook. I have a simple formula which can get me half way there, but I can't work out how to restrict it to only looking at the first release date against a product. I keep trying to use MIN, but I get the error about mixing agg and non-agg, which I don't really understand.

I suspect a LOD is required to exclude any but the first release date per product, but I can't get there alone.

So the value for the "Almost Right" calculated field should return 11 for Product 1 and 50 for Product 2. Product 2 is currently right, but Product 1 is 100 off.

Many thanks in advance for anyone who can help a little bit!

Sam

• ###### 1. Re: Help with LoD + Aggregates in IF statements

Samuel,

You could create an LOD calc that gives you whether a given Release date is the first one for a product.  The code would look something like this:

[Release Date] == {FIXED [Title] : MIN([Release Date])}

Then, you could simply use that as a filter to keep only the first release dates and your current calculation should work fine.

Best Regards,

Joshua

2 of 2 people found this helpful
• ###### 2. Re: Help with LoD + Aggregates in IF statements

Thanks Joshua! Amazing.

• ###### 3. Re: Help with LoD + Aggregates in IF statements

You're welcome! Glad to help!