Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hopkinsc
Valued Contributor II

Excluding specific reason from chart

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

Tags (1)
11 Replies
pat_agen
Valued Contributor

Excluding specific reason from chart

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
Valued Contributor II

Excluding specific reason from chart

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
Valued Contributor

Excluding specific reason from chart

hi,

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

hopkinsc
Valued Contributor II

Re: Excluding specific reason from chart

Hi,

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

Wastage.JPG

pat_agen
Valued Contributor

Excluding specific reason from chart

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
Valued Contributor II

Re: Excluding specific reason from chart

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?

erich_shiino
Honored Contributor

Re: Excluding specific reason from chart

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
Valued Contributor II

Re: Excluding specific reason from chart

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
Valued Contributor II

Re: Excluding specific reason from chart

Any ideas on my problem?

Community Browser