Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis nested

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps this:

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


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
MK_QSL
MVP
MVP

Can you provide some sample data?

Clever_Anjos
Employee
Employee

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

tresesco
MVP
MVP

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

Gysbert_Wassenaar

Perhaps this:

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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)

or

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.

CELAMBARASAN
Partner - Champion
Partner - Champion

Try this

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

Not applicable
Author

I also believe that this expression is correct!!!

Not applicable
Author

Thank you very much for all your replies !