topic Re: Help - suppress nulls in QlikView

Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 20:07:56 GMT

Hi all,

I'm a bit stuck. Let me introduce my challenge with below graph.

I don't want May-2016 in my analysis because I only want closed months and not the running month.This means i want a Null in May which are suppresed

M36Flag is used flag the last 36 closed months

Two expressions are used:

Volume=If(M36Flag=0,Null(),sum($(VarFact))/1000*$(VarFactor))

Trend= If(M36Flag=0,Null(),below( sum({<M36Flag={1}>} $(VarFact)),0,12) /1000*VarFactor)

The first works fine, but I dont understand why Trend is not Null in May2016.

Any suggestions?

thanks Joris

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 20:19:06 GMT

Are you sure May-2016 have M36Flag = 0? Is there a possibility that it has 0 and 1 both assigned to it?

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 20:25:37 GMT

i'm pretty sure, yes. And because the logic is correct in Volume i'm confused.

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 20:34:00 GMT

Null in volume could be because of the expression itself. Can you create a straight table with the same dimension as this chart and add two expressions:

Count(DISTINCT M36Flag) and

Concat(DISTINCT M36Flag, ', ')

and share a screenshot for May-2016

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 20:37:14 GMT

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 20:42:40 GMT

Can you add the trend expression to the above straight table and see if you get null or not?

If(M36Flag=0, Null(), Below(Sum({<M36Flag={1}>} $(VarFact)),0,12) /1000*VarFactor)

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 20:46:33 GMT

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 20:50:12 GMT

I also have a background color expression.

if(Monthstart(MonthYear)<=AddMonths(Today(),-Count(Distinct TOTAL(MonthYear))+13),ARGB(0,252,115,98))

Would that cause issues?

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 20:59:43 GMT

Ya, why don't you try adding the same if condition to your background expression as well:

If(M36Flag=0, if(Monthstart(MonthYear)<=AddMonths(Today(),-Count(Distinct TOTAL(MonthYear))+13),ARGB(0,252,115,98)))

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 21:03:30 GMT

Doesn't work.

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 21:15:20 GMT

That is just so strange... Would you be able to post a sample? Or may be try recreating the chart from scratch and try with the trend expression first

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 21:22:24 GMT

Bingo!

Re: Help - suppress nulls

joris_lansdaal — Tue, 31 May 2016 21:25:53 GMT

Thanks man!

Re: Help - suppress nulls

sunny_talwar — Tue, 31 May 2016 21:30:12 GMT

Adding back other expressions and properties doesn't break the chart? I hope not