topic Doubt with expression in QlikView

Doubt with expression

Tue, 26 Apr 2011 18:26:25 GMT

Dears,

Actually I have this expression in my bar chart.

=SUM({<UNIQUE_FLAG={1}>}([Labour Hours]*[Labor Rate])*pick(match([Currency],'EUR','USD','BRL'),EUR,USD,BRL))

But I would like to insert the creteria (IF) to sum only if the column [Status Claim] contains the valeu "Open", "Closed", or "Submitted". If the value was "Cancelled" they should not sum the value of this line.

How can I do it?

In excel it would be somehitng like: Sumif or Sumifs

Thanks for your attention

Doubt with expression

erichshiino — Tue, 26 Apr 2011 18:30:50 GMT

if it is already on your chart,

you can just write:

=if ( [Status Claim] <> 'Cancelled', sum(Value),0)

Doubt with expression

Tue, 26 Apr 2011 18:34:11 GMT

Where should I write it?

At the script manager?
Expression writer? If so, what should I do with my other expression, the one I sent you before?

Doubt with expression

Tue, 26 Apr 2011 18:37:39 GMT

I tried: =if([Status Claim] <> 'Cancelled', SUM({<UNIQUE_FLAG={1}>}([Labour Hours]*[Labor Rate])*pick(match([Currency],'EUR','USD','BRL'),EUR,USD,BRL)),0) and it didn't worked... continue showing the same number as shown in the previos expression I sent you!

Doubt with expression

erichshiino — Tue, 26 Apr 2011 18:45:49 GMT

I understood the ([Status Claim] was one of many of your expressions in your table.

If it is a field on your model, you can include another condition on set analysis:

SUM({<UNIQUE_FLAG={1}, [Status Claim] -={Cancelled}>}([Labour Hours]*[Labor Rate])*pick(match([Currency],'EUR','USD','BRL'),EUR,USD,BRL)),0)

Doubt with expression

Tue, 26 Apr 2011 18:49:44 GMT

it's giving the error: Garbage after ","

when I tried to use the follow expression

=SUM({<UNIQUE_FLAG={1}, [Status Claim] -={Cancelled}>}([Labour Hours]*[Labor Rate])*pick(match([Currency],'EUR','USD','BRL'),EUR,USD,BRL)),0)

Doubt with expression

erichshiino — Tue, 26 Apr 2011 18:52:50 GMT

sorry,

I copied a bit more than I should from your original expression:

SUM({<UNIQUE_FLAG={1}, [Status Claim]-={Cancelled}>}([Labour Hours]*[Labor Rate])*pick(match([Currency]

,'EUR','USD','BRL'),EUR,USD,BRL))

Doubt with expression

Tue, 26 Apr 2011 18:52:55 GMT

I've fixed the problem and now it's working.

Thanks for your help!