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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What's wrong in this expression?

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
p145-1
p29891
p348-1

p4

874
p178-1
p189
p2677674
p376-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!

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

5 Replies
Not applicable
Author

Why not just use if statements?

=if(sum(product_cost)<=$(MAX) AND sum(product_cost)>=$(MIN) AND Value>0, Product_code)

Not applicable
Author

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

Not applicable
Author

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

Miguel_Angel_Baeyens

Hello Igor,

What about the following

sum( {$<Product_Code =  {'=Sum(product_cost) <$(MAX) '} - {'=Sum(product_cost) < $(MIN)'}, Value -= {0} >} product_cost)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

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)