Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Any help please?

4 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

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

Hope this helps you.

Regards,

Jagan.

Gysbert_Wassenaar

Try:

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

Replace Year, Customer and Sales with your field names.


talk is cheap, supply exceeds demand
Not applicable
Author

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])

Not applicable
Author

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