aggr all products with more than 100 quantity

[mizrahimic9]

hi all,

i am a new user of qlik so maybe the question is a bit dumb but i couldnt find any answer.

i have a "sales" table by "orders" and i want to sum by "Product" but only the products with more than 100 quantity.

for example - 

order_ID	Product	quantity	sales
1	AAA	30	150
2	BBB	50	50
3	CCC	250	30
4	AAA	30	150
5	AAA	50	250
6	BBB	40	10

this is the table, the result needs to be - 580 - only 1 number

AAA(quantities) = 30+30+50 = 110 

BBB(quantities) = 40 + 50 = 90

CCC(quantities) = 250

so it needs to sum AAA(sales) = 550 + CCC(sales) = 30 = 580

hope i expressed myself correct.

thank you!!

[lanlizgu]

where do you want to do it? in the script or set analysis?

[mizrahimic9]

HI,

in script.

thank you!!

[lanlizgu]

Table1:
load
sum(sales) as sales
where quantity>100
;
load
Product,
sum(quantity) as quantity,
sum(sales) as sales

group by Product

;
LOAD * INLINE [
order_ID, Product, quantity, sales
1, AAA, 30, 150
2, BBB, 50, 50
3, CCC, 250, 30
4, AAA, 30, 150
5, AAA, 50, 250
6, BBB, 40, 10
];

[mizrahimic9]

i'm so sorry - i probably didn't understood you.  i will try to explain better - please see picture.

it work for me when i use product in the table,

but not if I choose other dimension like Group - I expect it to aggregate all products with more than 100 but instead it is doing the calculation on every row by group.

(please notice - in the picture I changes field names to English (originally language is Hebrew - RTL))

sorry for not being accurate - hope now it is better.

[lanlizgu]

ok, so you want it for set analysis, not script