topic Re: Excluding specific reason from chart in QlikView

Excluding specific reason from chart

hopkinsc — Wed, 22 Jun 2011 10:22:13 GMT

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

Excluding specific reason from chart

pat_agen — Wed, 22 Jun 2011 10:27:01 GMT

hi,

try

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

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

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

Excluding specific reason from chart

hopkinsc — Wed, 22 Jun 2011 10:37:09 GMT

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?

Excluding specific reason from chart

pat_agen — Wed, 22 Jun 2011 10:42:29 GMT

hi,

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

Re: Excluding specific reason from chart

hopkinsc — Wed, 22 Jun 2011 10:48:21 GMT

Hi,

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

Excluding specific reason from chart

pat_agen — Wed, 22 Jun 2011 10:58:27 GMT

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.

Re: Excluding specific reason from chart

hopkinsc — Wed, 22 Jun 2011 11:08:53 GMT

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?

Re: Excluding specific reason from chart

erichshiino — Wed, 22 Jun 2011 11:44:54 GMT

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

Re: Excluding specific reason from chart

hopkinsc — Thu, 23 Jun 2011 08:44:47 GMT

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?

Re: Excluding specific reason from chart

hopkinsc — Thu, 23 Jun 2011 11:53:19 GMT

Any ideas on my problem?

Excluding specific reason from chart

Thu, 30 Jun 2011 15:19:38 GMT

Hi hopkinsc

Maybe you can use a Calculated Dimension instead the expresión with Set Analysis.

IF(MATCH([Narrative],'WASTAGE','DAMAGED')= 0, [Narrative])

The WASTED and DAMAGED data willl show with NULL value, then just you can Suppress the dimension when value is null.

Hope this help

Regards.

Excluding specific reason from chart

erichshiino — Thu, 30 Jun 2011 15:44:30 GMT

You can include another field in your load statement:

Load ...

Narrative,

If( Narrative = 'WASTAGE' or Narrative='DAMAGED', 1, 0) as flagNarrative

FROM / Resident ....

Then, using set analysis on the chart:

sum ( {<flagNarrative ={0}>} StockValue) /

sum ( {<flagNarrative ={0}>} StockQty)

Hope this helps,

Erich