Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (3)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set analysis nested

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
Highlighted
MVP
MVP

Re: Set analysis nested

Can you provide some sample data?

Highlighted
Employee
Employee

Re: Set analysis nested

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

Highlighted
MVP
MVP

Re: Set analysis nested

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set analysis nested

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

Highlighted
Creator III
Creator III

Re: Set analysis nested

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.

Highlighted

Re: Set analysis nested

Try this

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

Highlighted
Not applicable

Re: Set analysis nested

I also believe that this expression is correct!!!

Highlighted
Not applicable

Re: Set analysis nested

Thank you very much for all your replies !