topic Re: What's wrong in this expression? in QlikView

What's wrong in this expression?

Tue, 31 May 2011 16:31:59 GMT

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!

What's wrong in this expression?

Tue, 31 May 2011 17:26:14 GMT

Why not just use if statements?

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

What's wrong in this expression?

Tue, 31 May 2011 17:40:10 GMT

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

Re: What's wrong in this expression?

Wed, 01 Jun 2011 09:46:42 GMT

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

Re: What's wrong in this expression?

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 09:52:29 GMT

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

Re: What's wrong in this expression?

Wed, 01 Jun 2011 12:18:03 GMT

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)