Can I embed an IFNULL in a Formula Builder Calculated field?
24 November, 2014
Hi
I have a calculated field in a view called Balance, that sums three columns from two different tables. Screenshot attached. The SQL equivalent is:
bf.bfwd + bf.appropriated + ytd.ytd_total
In some valid instances there is no entry for an account in ytd.total.
So effectively I want to achieve:
bf.bfwd + bf.appropriated + ifnull(ytd.ytd_total,0)
Otherwise I'm finding that some accounts are not coming through in report results.
Is there a way I can achieve this using the Formula Builder?
Thanks and regards
Paula
I have a calculated field in a view called Balance, that sums three columns from two different tables. Screenshot attached. The SQL equivalent is:
bf.bfwd + bf.appropriated + ytd.ytd_total
In some valid instances there is no entry for an account in ytd.total.
So effectively I want to achieve:
bf.bfwd + bf.appropriated + ifnull(ytd.ytd_total,0)
Otherwise I'm finding that some accounts are not coming through in report results.
Is there a way I can achieve this using the Formula Builder?
Thanks and regards
Paula
Hi there
Sorry to chase. But keen to find an answer to this one if you have a chance.
Thanks and regards
Paula
Sorry to chase. But keen to find an answer to this one if you have a chance.
Thanks and regards
Paula
Hi Paula,
no worries, just use a CASE statement:
[code]
CASE WHEN ytd.ytd_total is null THEN 0 ELSE ytd.ytd_total END
[/code]
I don't see why that shouldn't work, please let us know how it goes.
regards,
Dave
no worries, just use a CASE statement:
[code]
CASE WHEN ytd.ytd_total is null THEN 0 ELSE ytd.ytd_total END
[/code]
I don't see why that shouldn't work, please let us know how it goes.
regards,
Dave
You are a STAR, Dave. Worked a treat, thank you very much.
Kind regards
Paula
Kind regards
Paula