if Value Null then

[#1]

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)))

[Anonymous]

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>)

[#3]

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

[Anonymous]

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

[#5]

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