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

    Help with LoD + Aggregates in IF statements

    Samuel Gee

      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