Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Any help please?
Hi,
Try like this
=Sum({<Customer={"=Sum({'Year={'>=$(Year(Today()) -1)<=$(Year(Today()))'}Sales) = 0"}>} Sales)
Hope this helps you.
Regards,
Jagan.
Try:
sum({<Year={'$(=year(today()))'}, Customer=e({<Year={'>=$(=year(today())-2)<$(=year(today()))'}>}Customer) >} Sales)
Replace Year, Customer and Sales with your field names.
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])
Any help on this guys? I'm helplessly stuck.