2 Replies Latest reply on Oct 30, 2018 2:13 PM by Penn Wang

# Show 0 when [Number of Records] is 0

Hi Everyone,

I have a calculation field to calculate the number of records under certain condition

1. [Difference] column less than -6

4. Not start with Grape and Delivery City is not the same as Destination City

How does it show 0 when the number of affected rows is 0? Now it just return me nothing so I can't use the result in further calculation. Thank you.

SUM

(

IF ([Difference])<-6

AND (NOT STARTSWITH([Product], 'Apple')

AND (NOT STARTSWITH([Product], 'Orange'))

AND NOT ((STARTSWITH([Product], 'Grape') AND [DeliveryCity] = [DestinationCity])))

THEN ([Number of Records])

END

)

I found out the answer for some similar questions is the ZN function, but I'm not sure how to use it in my case.

• ###### 1. Re: Show 0 when [Number of Records] is 0

Does this work ?

SUM

(

IF ([Difference])<-6

AND (NOT STARTSWITH([Product], 'Apple')

AND (NOT STARTSWITH([Product], 'Orange'))

AND NOT ((STARTSWITH([Product], 'Grape') AND [DeliveryCity] = [DestinationCity])))

THEN ([Number of Records])

ELSE 0

END

)

1 of 1 people found this helpful
• ###### 2. Re: Show 0 when [Number of Records] is 0

Thanks! It works!