topic Re: Suppress missing or zero values is not working in QlikView

Suppress missing or zero values is not working

mohit1947 — Fri, 08 Jul 2016 08:43:34 GMT

Hi All,

I have YOY % calculation where I using below farmula.

if( fabs($(PY)) > 1 ,(( $(YTD) - $(PY) )/ $(PY)),null())

But it's still showing that null value in Table.

If you look at the value of %YOY column there is null value is coming for new. If i disable YOY column it is suppressing the value the row for new.

One more thing i noticed it's suppressing the value of book 5 under new but not new(as total).

It's a straight table i m using over here.

Can some body help me in this?

Thanks

Re: Suppress missing or zero values is not working

sushil353 — Fri, 08 Jul 2016 09:48:22 GMT

Hi,

Try to suppress null and zero both in dimension and presentation tab.

Re: Suppress missing or zero values is not working

sunny_talwar — Fri, 08 Jul 2016 10:21:50 GMT

Have you tried adding more decimals to YTD, F'cast, 2015 to check if one of those rows have really small decimal number? May be something like 0.0000000015 which is forcing the "New" row to stay?

Re: Suppress missing or zero values is not working

mohit1947 — Fri, 08 Jul 2016 12:56:51 GMT

I have tried that. But no luck.

Thanks

Re: Suppress missing or zero values is not working

mohit1947 — Fri, 08 Jul 2016 12:57:55 GMT

Hi Sunny,

I have tried below expression still. It is not working.

if( fabs($(PY)) = 0, null() ,(( $(YTD) - $(PY) )/ $(PY)))

Re: Suppress missing or zero values is not working

sunny_talwar — Fri, 08 Jul 2016 14:38:56 GMT

How about this:

If(Round($(PY)) = 0, Null() ,(( $(YTD) - $(PY) )/ $(PY)))

Re: Suppress missing or zero values is not working

mohit1947 — Sat, 09 Jul 2016 03:53:08 GMT

Hi Sunny,

I have Tried that also but not working.

If(Round($(PY)) = 0, Null() ,(( $(YTD) - $(PY) )/ $(PY)))

I am not sure how qlikview is evaluating the if expression. if PY is 0 and we are defaulting it to null(). Don't why it's showing missing value null.

One thing I observed It's eliminating lowest dimension value but not the total at second dimension level. When I am writing If(Round($(PY)) = 0, Null() ,1), It's ignoring all the suppress value but when we writing expression it's not working.

Is it because of expression qlikview evaluating in else part at second level of dimension.

Thanks.

Re: Suppress missing or zero values is not working

sunny_talwar — Sat, 09 Jul 2016 12:14:38 GMT

Can you try this:may be:

If(Round($(PY)) = 0 or Round((($(YTD) - $(PY))/ $(PY))) = 0, Null() , (($(YTD) - $(PY))/ $(PY)))

Re: Suppress missing or zero values is not working

boorgura — Sat, 09 Jul 2016 15:29:27 GMT

Can you try this:

if( fabs($(PY)) > 1 ,(( $(YTD) - $(PY) )/ $(PY)),0)

Re: Suppress missing or zero values is not working

mohit1947 — Mon, 11 Jul 2016 13:48:40 GMT

Hi Sunny,

It's also not working.

Thanks,

Mohit

Re: Suppress missing or zero values is not working

sunny_talwar — Mon, 11 Jul 2016 14:16:50 GMT

Would sharing a sample a possibility?