Excluding specific reason from chart

[hopkinsc]

Hi, can anyone tell me how i would write an expression to show me waste values but excluding a specific reason please?

My fields are :

StockValue

Narrative

There are 2 waste Narrative reasons:

Wastage and WASTAGE

I want to exclude the uppercase WASTE from my results (so i don't want them being added up in the total and i don't want them to display in the chart)

Currently i have a simple expression...

Sum(StockValue)

but this show's me both Narratives. I don't want to have a list box for the user to just select Wastage as our average users wouldn't know the difference between the 2 Narratives.

Basically WASTAGE is a system generated reason when the user downloads waste figures using an older program (this program should only be used for FREEZER BREAKDOWNS but now and again some of our customers use it by mistake)

Wastage is the reason generated using the correct program.

Any help would be appreciated..

Thanks

[pat_agen]

hi,

try

     sum(if(Narrative='Wastage',StockValue))

or

     sum({<Narrative={Wastage}>}  Stockvalue)

both should do the job. The set analysis will be better eprformance wise.

[hopkinsc]

Hi, thanks for your reply.

The first expression works but it still displays the WASTAGE lines in the chart (but with ZERO value) Is there a way i can completely stop the line from showing?

The set analysis expression just displays ZERO's for StockValue...

Any ideas?

[pat_agen]

hi,

check that on the presentation tab of the chart->properties dialogue that you are suppressing both missing and zero values.

[hopkinsc]

Hi,

Yes, they are both ticked... but still showing the ZERO values as you can see from the attached image..

[pat_agen]

hi,

you have to apply the if() caluse to every expression in your cahrt. it looks like you have other expressions, most notably the display of the Narrative field which will cause every expression to be evaluated.

The if() calsue is forcing the expression to null but it is being displayed because the row is being displayed.

apply the if() everywhere or remove the other columns.

[hopkinsc]

ok, thanks, that makes sense now..

I have altered your expression to :

sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockValue))

Which seems to work, i will add the same IF statement to my other expressions.

one of my expressions is currently

sum(StockValue)/Sum(StockQty)

how would i incorporate the IF statement into that?

I have tried

sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockValue)/Sum(StockQty))

but doesn't seem to work..

Would the IF need to be on both StockValue and StockQty?

[erichshiino]

Hi, you could try:

sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockValue)/Sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockQty))

but you can also write it as a set analysis expressions (which may be a better performance.

The syntax will be:

sum  ( {<Narrative -={WASTAGE, DAMAGED}>} StockValue) /

sum  ( {<Narrative -={WASTAGE, DAMAGED}>} StockQty)

Hope this helps,

Erich

[hopkinsc]

Hi, thanks for your reply, but i can't get either examples to work.

This expression doesn't display any figures..

sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockValue)/Sum(if(Narrative<>'WASTAGE'and Narrative<>'DAMAGED',StockQty))

and this expression displays incorrect figures...

sum  ( {<Narrative -={WASTAGE, DAMAGED}>} StockValue) /

sum  ( {<Narrative -={WASTAGE, DAMAGED}>} StockQty)

Any ideas on how to fix this?

[hopkinsc]

Any ideas on my problem?