topic Re: if Value Null then in QlikView

if Value Null then

Mon, 12 May 2014 18:46:55 GMT

Hello,

I have a Pivot table and I am trying to supress some values based on other columns results. For example if I modify my current formular to look like the sample below (I added the GF<>Null part), my Pivot look like the screen shot below. That is doing exactly what is suppossed to but I cannot find a way to reverse that. What I need is that if value on Column GF is Null, then value for 'Rolling 6 Months' should also be Null. I tried: If(IsNull(GF),Null() but it doesn't work as a curiosity I wrote my formula the opposite if not Null but this is not what I want. How can I start my formula to analyze if Value from GF is Null, then Null?

if(GF<>Null(),Null(),
if('$(vViewMode)'='Units',
Sum({$<PeriodID ={">$(vPreviousPeriodID(6))"},Year=,Month=,Collection-={'Others'}>}qty)
,
sum({$<PeriodID ={">$(vPreviousPeriodID(6))"},Year=,Month=,Collection-={'Others'}>} $(vNetAmount)*RateConverted)))

Re: if Value Null then

Anonymous — Mon, 12 May 2014 20:03:46 GMT

Is GF a field or an expression?

If it is a field, your IsNull should work, although I prefer len(trim()):

if(len(trim(GF)), <your expression for rolling 6 months here>)

Re: if Value Null then

Mon, 12 May 2014 20:54:51 GMT

Hi Michael,

my GF column is an expression, that returns Null if the calculation total is >-0.15. Basically what are trying to achieve is to show only records below -15%, that's why on GF if the condition is not met, then Null. Now Rolling 6 Months, if GF is Null, then it should be Null as well.

Thanks

Re: if Value Null then

Anonymous — Tue, 13 May 2014 13:01:32 GMT

Hugo,

Can you upload an example? Without it, another blind guess:

Use the full GF expression within the Rolling 6 months, e.g.

IF( <GF expression> <=-0.15,

<Rolling 6 Months expression>,

null()

)

Regards,

Michael

Re: if Value Null then

Wed, 14 May 2014 02:24:30 GMT

Thank you Michael,

I tried to follow your suggestion but I couldn't get to work since I am using Set Analysis, however your suggestions help me realize a solution. I replace (if(GF<>Null(),Null()) for 3 different ifs that check for values on the 3 columns and mark them as Null if any of the 3 conditions are not met. in the end I got the results I wanted.

Thanks,

Hugo