Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
where do you want to do it? in the script or set analysis?
HI,
in script.
thank you!!
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
];
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.
ok, so you want it for set analysis, not script