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

    Null as 0 then Supress Small Numbers

    Krista Concannon


      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!