topic Re: Set analysis nested in QlikView

Set analysis nested

Wed, 18 Jun 2014 12:18:39 GMT

Hello everyone,

Can you help me about a nested set analysis ?

I want to get the sum of invoices from a department, only for certain products.

To do that, I tried to use :

=Sum({$<DEPARTMENT={"Sum({$<PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])>'DEPARTMENT_NUMBER'"}>}[AMOUNT_INVOICED])

But it doesn't work.

Can you help me to solve this ?

Thanks lot

Re: Set analysis nested

MK_QSL — Wed, 18 Jun 2014 12:51:36 GMT

Can you provide some sample data?

Re: Set analysis nested

Clever_Anjos — Wed, 18 Jun 2014 13:00:01 GMT

Try this

=Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])>10000 "}>}[AMOUNT_INVOICED])

This will retrieve sum of all amount where sum of product1 and product2 > 10.000

10.000 can be a variable

=Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])>$(=some_var)"}>}[AMOUNT_INVOICED])

Re: Set analysis nested

tresB — Wed, 18 Jun 2014 13:00:12 GMT

Put an '=' sign in the inner sum() and single quotes for products, like :

Sum({$<DEPARTMENT={"=Sum({$<PRODUCT_FAMILY={'PRODUCT_1','PRODUCT_2'}>}[AMOUNT_INVOICED])> DEPARTMENT_NUMBER "}>}[AMOUNT_INVOICED])

Re: Set analysis nested

Gysbert_Wassenaar — Wed, 18 Jun 2014 13:06:01 GMT

Perhaps this:

=Sum({$<DEPARTMENT={'DEPARTMENT_NUMBER'},PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])

Re: Set analysis nested

Anonymous — Wed, 18 Jun 2014 13:07:52 GMT

Hello!

I don't get what the DEPARTMENT_NUMBER have to do with the sum of AMOUNT_INVOICED. Why compare them?

Anyway, if you want the sum of invoices only for certain products (or families), and view it by department, you need to us DEPARTMENT as a dimension and this expression:

Sum({$<PRODUCT_FAMILY = {'PRODUCT_FAMILY_1', 'PRODUCT_FAMILY_2'}>} AMOUNT_INVOICED)

Sum({$<PRODUCT = {'PRODUCT_1', 'PRODUCT_2'}>} AMOUNT_INVOICED)

Notice one expression is on the level of the product families and the other on the level of the products themselves. Mixing them wouldn't work.

But if what you want is the total sum of invoices of all products of the departments that have sold certain products, the expression would be like:

=Sum({$<DEPARTMENT = {P({$<PRODUCT = 'PRODUCT_1', 'PRODUCT_2'}>} DEPARTMENT)}>} AMOUNT_INVOICED)

Adjust for PRODUCT_FAMILY as required.

Hope it helps.

Regards.

Re: Set analysis nested

CELAMBARASAN — Wed, 18 Jun 2014 13:17:29 GMT

Try this

=Sum({$<PRODUCT_FAMILY={"PRODUCT_1","PRODUCT_2"}>}[AMOUNT_INVOICED])

Re: Set analysis nested

Wed, 18 Jun 2014 14:08:44 GMT

I also believe that this expression is correct!!!

Re: Set analysis nested

Thu, 19 Jun 2014 07:51:04 GMT

Thank you very much for all your replies !