topic Set Analysis Help in QlikView

Set Analysis Help

Tue, 07 May 2013 16:38:11 GMT

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?

Re: Set Analysis Help

jagan — Tue, 07 May 2013 16:43:34 GMT

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

Gysbert_Wassenaar — Tue, 07 May 2013 16:48:40 GMT

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

Wed, 08 May 2013 14:20:44 GMT

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

Thu, 09 May 2013 15:18:13 GMT

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