1 Reply Latest reply on Dec 8, 2016 5:43 PM by Shinichiro Murakami

# Null as 0 then Supress Small Numbers

Hello,

I am working with health care data and need to suppress non-zero small numbers (less than 10), but first I need to create the 0's.  I can't quite figure out how to do both, and I'm too stubborn to go back to my data to put in the 0's first.

I found in another discussion how to turn blank spaces into 0's with this calculated field (call it Total): zn(lookup(sum([Measure]),0))

That works great, but now I need to hide small numbers. I have been doing it by creating a calculated filed like this: IIF(sum([Measure])>=10 or sum([Measure])=0,sum([Measure]),null)

I tired the following, but you can't aggregate an aggregate: IIF(sum([Total])>=10 or sum([Total])=0,sum([Total]),null)

I also tried to do a nested IFF using the two different functions, but kept getting syntax errors.  Does anyone know how to do this?

Sorry I can't attach my current workbook.  I will work on creating a dummy example after I make it home through the snow

Thank you!

• ###### 1. Re: Null as 0 then Supress Small Numbers

Hi Krista

I don't understand what do you mean by "hide". I tried to make it Zero for now. and hide "0".

But conflict with showing 0 on null ????

Couple of steps.

[ZN lookup]

zn(lookup(sum([Value]),0))

[ZN with <1 = 0]

zn(lookup(sum(if [Value]>=1 then [Value] end),0))

[ZN with <1 = 0 hide 0]

[ZN with <1 = 0]

Then change the format

Thanks,

Shin