Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear QV users,
I am trying to use an expression, but i have a problem...
i have a table like this:
Product_Code | product_cost | Value |
---|---|---|
p1 | 45 | -1 |
p2 | 989 | 1 |
p3 | 48 | -1 |
p4 | 87 | 4 |
p1 | 78 | -1 |
p1 | 8 | 9 |
p2 | 6776 | 74 |
p3 | 76 | -99 |
And I want to see as result only product_code which has a sum between 'Max' and 'Min' ( it's work ), AND only if the current row has a Value field > 0.
I use this:
sum( {$<Product_Code = {'=Sum(product_cost) <$(MAX) '} - {'=Sum(product_cost) < $(MIN)'} - {'Value < 0'} >} product_cost)
{'Value < 0'} doesn't work, i think it's a syntax error but nothing... i am not able to solve this problem.
Any suggest?
p.s.
i use also this one
sum( {$<Product_Code = {'=Sum({$<Value = {">0"}>} product_cost) <$(MAX) '} - {'=Sum({$<Value = {">0"}>}product_cost) < $(MIN)'} >} product_cost)
but unfortunatly the result is the same...
Thanks!
Hi all,
for your information i found the solution:
It works:
if(sum( { $<Value = {">0"}>} product_cost)<=$(Max) AND sum ({ $<Value = {">0"}>} product_cost)>=$(Min) ,
sum({$<Value = {">0"}>} product_cost) )
And this one, too (similar to my first idea, Am Bradd it's possible to use twice sum in set analysis):
sum( {$<Product_code = {'=Sum({ $<Value = {">0"}>} product_cost) <=$(Max) '} - {'=Sum({$<Value = {">0"}>} product_cost) < $(Min)'}, Value = {">0"} >} product_cost)
Really. thanks a lot all for your reply !!! I have found the solution using also your suggest.
Bye & thanks
p.s.
Miguel Angel Baeyens:
... , Value -= {0} >} return me a syntax error (qlikview v 10.0)
Why not just use if statements?
=if(sum(product_cost)<=$(MAX) AND sum(product_cost)>=$(MIN) AND Value>0, Product_code)
Hi Igor ,
I think some times we can't use sum twice in a set analysis expression so
Can you try count instead of sum , may be it will work ....or else try if contd as Mr.Trent said above.
thanks a lot
bradd
Unfortunatly the expression that you suggest:
=if(sum(product_cost)<=$(MAX) AND sum(product_cost)>=$(MIN) AND Value>0, sum(product_cost) )
doesn't work.
It returns only the sum of product_cost for products that have ALL Value in ALL rows > 0.
I mean, if you see the table in my first post, i want as output for product: p1
:
Product | sum(product_cost)
P1 | 9 <--- This row has in my Table a Value > 0
but the expression doesn't return nothing about P1, because it has one or more rows with a Value < 0.
Thanks for any suggests
Hello Igor,
What about the following
sum( {$<Product_Code = {'=Sum(product_cost) <$(MAX) '} - {'=Sum(product_cost) < $(MIN)'}, Value -= {0} >} product_cost)
Hope that helps.
BI Consultant
Hi all,
for your information i found the solution:
It works:
if(sum( { $<Value = {">0"}>} product_cost)<=$(Max) AND sum ({ $<Value = {">0"}>} product_cost)>=$(Min) ,
sum({$<Value = {">0"}>} product_cost) )
And this one, too (similar to my first idea, Am Bradd it's possible to use twice sum in set analysis):
sum( {$<Product_code = {'=Sum({ $<Value = {">0"}>} product_cost) <=$(Max) '} - {'=Sum({$<Value = {">0"}>} product_cost) < $(Min)'}, Value = {">0"} >} product_cost)
Really. thanks a lot all for your reply !!! I have found the solution using also your suggest.
Bye & thanks
p.s.
Miguel Angel Baeyens:
... , Value -= {0} >} return me a syntax error (qlikview v 10.0)