Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
berkarmagan
Partner - Creator
Partner - Creator

Subquery possible?

Hi,

I m working on a pivot table and i ve a problem like this. First of all i ve a hierarchy of product group, each product group contains at least one product additional groups.My problem is I want to sum values of 3 rd and 4th add.group and Show in front of product group 1.

   

   

Product groupProdcut Add GroupValue
1110
1220
1330
2430
2540
3650

   

Product GroupCalculation
1sum(productaddgroup 4 and 5)
2sum(productaddgroup 1 and 6)
3sum(productaddgroup 2 and 3)
1 Solution

Accepted Solutions
maxgro
MVP
MVP

if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL Value),

if([Product group]=2, sum({$ <[Prodcut Add Group]={1,6}>} TOTAL Value),

if([Product group]=3, sum({$ <[Prodcut Add Group]={2,3}>} TOTAL Value)

)))

View solution in original post

5 Replies
marcus_sommer

It seems that your [Product Group] and [Product Add Group] aren't hierarchical but if they are you could use set analysis like:

sum({< [Product Add Group] = {4, 5}>} value)

- Marcus

berkarmagan
Partner - Creator
Partner - Creator
Author

Thanks for ur reply first but i need to get that result for Product group 1. In my pivot table i just have product group and calculated values. If i use directly sum({< [Product Add Group] = {4, 5}>} value) , i ll got the result like this becuse gropu 2 contains add group 4 and 5

   

Product GroupCalculation
270

according to that example i need this like below . On the db structure, Group 2 contains add group 4 and 5, but i need to Show this on the group 1's line and also I need to Show add group 1 and 6 on group 2's line. I think i sould use aggr() function to group them but i couldn't get sucsess.

   

Product Group
Calculation
130+40=70
210+50=60
330+30=60 
maxgro
MVP
MVP

if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL Value),

if([Product group]=2, sum({$ <[Prodcut Add Group]={1,6}>} TOTAL Value),

if([Product group]=3, sum({$ <[Prodcut Add Group]={2,3}>} TOTAL Value)

)))

berkarmagan
Partner - Creator
Partner - Creator
Author

Thanks Massimo it worked but i think if we use 'total' in expression we lose grouping. I mean I ve got one more colon before product group  as customer name. If I use "if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL Value)" this it sum all values for all customers by product groups, but i need to get that calculation with grouping by Customer.

Thanks for your patience, I m kind of new on qlikview.

berkarmagan
Partner - Creator
Partner - Creator
Author

OK I think I found it, thanks for ur help at all. I can group my datas with total function like this:

if([Product group]=1, sum({$ <[Prodcut Add Group]={4,5}>} TOTAL <Customer> Value)

Thanks again!