override value for '0'

Highlighted
Contributor

override value for '0'

Hi 

 

I have 3 Line items 

1st -expense(number format)

2nd-Override Value(number format)

3rd-Final Expense(number Format)

formula for final expense is IF OVERRIDE  VALUE > 0 THEN OVERRIDE VALUE ELSE EXPENSE

So if want to override expense value with 0 then how can I modify the formula for final expense with out using any extra line item for the user to click 

Thanks 

Tejaswini

3 REPLIES 3
Highlighted
Certified Master Anaplanner

Re: override value for '0'

Hi @tejaswini 

 

You will need an additional line item formatted as boolean. Then you can amend the formula in the final expenses line item so that when the user ticks the box in the new boolean line item the final expenses value is overriden to zero.

 

 

Highlighted
Certified Master Anaplanner

Re: override value for '0'

Yes agree, the only way is to have the Boolean to say you want to use the Override number.  Also using > 0 would not allow an override with a negative number either so a Boolean is always best for Overrides.

Highlighted
Certified Master Anaplanner

Re: override value for '0'

Hi @tejaswini

An alternative (though probably not a great solution) is to set the override as a text-formatted line item.

When a user enters 0 into that line item, you can evaluate it in the 3rd line item with VALUE(x) to determine if there's a number there.

Formula : IF ISBLANK(override) THEN initial value ELSE VALUE(override)

Override 0.PNG

This does not prevent users from entering non-numeric values, which will evaluate to NaN in the final value. This may be preferable if you're trying to alert the end user that it's invalid.

However, if you want to default to the initial value, you can add an additional evaluation IF ABS(VALUE(override)) >= 0 THEN... This will check to see if the override evaluates to a number, and if it doesn't, default to initial value.