1 2 Previous Next 16 Replies Latest reply on Sep 12, 2017 1:08 AM by T G

# Remove Zero from Result

Hello,

I've AR ageing balance report. There are many zeros coming in the report.I would like to remove it from bucket i made. but when i put BALANCE <> 0 , I'm missing values from other buckets. Below is the excel o/p of report. The Zero containing list is big and that's why report is getting very big.

• ###### 1. Re: Remove Zero from Result

Please share a sample workbook to help to better. Still, you can use below formula

IIF([Measures]=0,'',STR([Measure]))

• ###### 2. Re: Remove Zero from Result

Sounds like you want of filter out 0s, but only where 6m-2y is NOT equal to zero, so create a calculated field defining that. call it "filter zeroes"

if [2y+] <> 0 and [6m-2y] <> 0 then "Y" else "N" end

then drag the filter zeroes pill to filters and select only "Y".

• ###### 3. Re: Remove Zero from Result

Hello,

This is converting measure to dimension due to STR.

• ###### 4. Re: Remove Zero from Result

When i apply this formula, this only N value is coming in filter and result is different.

6m-2yr is calculated field on Outstanding Amount Usd like below.

if TODAY() - [Due Date] >=181 and  TODAY() - [Due Date] <=730 then

[Outstanding Amount Usd]

END

and 2yr+ is directly based on a view column [Above 2 Years]

• ###### 5. Re: Remove Zero from Result

I see i've 2 other columns(Outstanding Amount Usd and Amount Remaining Usd ) which has data for Parties and if i don't add them in report, the party name is still appearing without any data.

• ###### 6. Re: Remove Zero from Result

Hello Nitin,

Sorry for late reply. You can use IIF([Measures]=0,NULL,[Measure]).

TG

• ###### 7. Re: Remove Zero from Result

Same Result.

• ###### 8. Re: Remove Zero from Result

It would be better if you can share your workbook.

• ###### 9. Re: Remove Zero from Result

attached.

• ###### 10. Re: Remove Zero from Result

Please send the workbook with extracted not live connection.

• ###### 11. Re: Remove Zero from Result

Please restate the problem in terms of the visible rows and columns, or indicate if the problem is in terms of a non-visible measure.

BALANCE does not exist in the view.

You want party name to be filtered out where what?

(    EPCI 2y+ = 0

and Grand Total 2y+ = 0)

but keep if

(EPCI [6m-2y] <> 0

or and Grand Total [6m-2y] <> 0)?

I don't understand the problem.

• ###### 12. Re: Remove Zero from Result

What i'm looking is parties to be displayed who have numbers (-ve or +ve) for all measures mentioned.

i don't want parties with blank or null or zeros numbers.

Regards!

• ###### 13. Re: Remove Zero from Result

Attached with extract.

• ###### 14. Re: Remove Zero from Result

Hello Nitin,

I can see the actual value of 0 is not the 0 its -0.0200000. That is why nobody get this done correct. So, you have to use ROUND().

Refer the below snapshot and I have attached the updated workbook 10.1. I have created a calculated field for 6m-2yr named Color Code (6m-2yr) as

IF ROUND([6m-2yr])>0 THEN 'Green'

ELSEIF ROUND([6m-2yr])<0 THEN 'Red'

ELSE 'White'

END

You can create as many as you want for other measures. I hope this could help you.

TG

1 2 Previous Next