topic Re: Hide lines based on formula in QlikView

Hide lines based on formula

Fri, 26 Jun 2015 09:44:01 GMT

I have a straight tabe with some dimensions like order number, customer name etc. and some formulas that calculate the produced quantity, the delivered quantity and the ready for dispatch quantity. The dispatch quantity is simply "produced minus delivered". This results in a table that looks like this:

I want to get rid of all lines in this table where the "Ready for dispatch" column has a "0" value:

Is there a nice way to do this in the properties somewhere? I tried my luck with the surpress zero values, but this did not work out the way I wanted.

Many thanks for your help!

Günter

Re: Hide lines based on formula

pokassov — Fri, 26 Jun 2015 09:49:52 GMT

Hi!

if([Ready for dispatch(kg)]>0,put_here_your_expressions_for_each_column).

For example, I have 3 expressions:

sum(sales),

count(id),

max(days) <--- this I want to use for check zero

then

if(max(days)>0,sum(sales))

if(max(days)>0,count(id))

max(days)

Re: Hide lines based on formula

Anonymous — Fri, 26 Jun 2015 09:53:38 GMT

please send us the expression you use

Re: Hide lines based on formula

Fri, 26 Jun 2015 10:06:37 GMT

You can try in all expression,

if(sum(Produced)<>sum(Delivered),.......required expression, null()).... PFA

Re: Hide lines based on formula

Fri, 26 Jun 2015 10:07:03 GMT

U can write like this (if u r trying to show them in straight table in qlik)

if(([produced(kg)]-[delivered])>0,([produced(kg)]-[delivered]))

dont put anything in else part ...and give ok

make sure that u shd have write these two exp (produced,delivered) before this exp..

Re: Hide lines based on formula

Anonymous — Fri, 26 Jun 2015 10:08:53 GMT

Hi Gunter ,

In Second your expected table in Dispatch qty column's expression you should write below code-

if(sum(ProducedQty) <> sum(DeliveredQty) ,sum(Produced)-sum(Delivered) ).

Hope this would be helpful.

Re: Hide lines based on formula

Fri, 26 Jun 2015 10:09:29 GMT

Hello Sergey,

this seems to be the right approach. I have my formulas based on your proposal which brings me to the correct table, but the totals are wrong...

This is again an Excel screenshot to show the difference. The first line shows the totals calculated by Excel (which are correct for the values shown) and the last line the total that comes from QlikView. Obviously the totals are not effected by the formula. How can this be changed?

Thanks,

Günter

Re: Hide lines based on formula

Fri, 26 Jun 2015 10:17:18 GMT

Seems that this solved the problem...

I have changed in the formula page the total value from "all values" to "sum" - everything works now perfectly fine. Thank you for all your contributions!