4 Replies Latest reply: May 9, 2013 11:18 AM by Adam Haithcox

# Set Analysis Help

I'm trying to sum the current year's sales IF that same customer didn't have sales (=0) for the previous two years. I've been trying to use set analysis expressions, but haven't been successful.

• ###### Re: Set Analysis Help

Hi,

Try like this

=Sum({<Customer={"=Sum({'Year={'>=\$(Year(Today()) -1)<=\$(Year(Today()))'}Sales) = 0"}>} Sales)

Hope this helps you.

Regards,

Jagan.

• ###### Re: Set Analysis Help

Try:

sum({<Year={'\$(=year(today()))'}, Customer=e({<Year={'>=\$(=year(today())-2)<\$(=year(today()))'}>}Customer) >} Sales)

Replace Year, Customer and Sales with your field names.

• ###### Re: Set Analysis Help

Thank you so much for you reply Gysbert. What you proposed seems to work! I've been working on this a long time, so thank you again.

But now, my boss has added some granularity to it; He wants it down to a product level. So:

Sum of current year's sales by product category ONLY if the same category didn't have ANY sales in ANY month for the prior two years. How would I achieve this? This is my current expression which doesn't seem to give my what I need:

sum({<[Fiscal Year]={'\$(FiscalCurrentYear)'},

[Sales Catalog Sub Section]=e({<[Fiscal Year]={'>=\$(=FiscalCurrentYear-2)<\$(FiscalCurrentYear)'}>}[Sales Catalog Sub Section]) >} [Sales Amount])

• ###### Re: Set Analysis Help

Any help on this guys? I'm helplessly stuck.