Hi, the following formula works fine: CASE WHEN {custrecord_deprhisttype} = 'Depreciation' THEN (CASE WHEN (to_char({custrecord_deprhistdate},'MM') < to_char({today},'MM')) AND (to_char({TODAY},'YY') = to_char({custrecord_deprhistdate},'YY')) THEN {custrecord_deprhistamount} ELSE NULL END) ELSE NULL END However I want to modify the formula to show 0.00 if {custrecord_deprhistamount} is negative. Can you please advise?

replace it with

GREATEST({custrecord_deprhistamount}, 0)

CASE WHEN {custrecord_deprhisttype} = 'Depreciation' THEN (CASE WHEN (to_char({custrecord_deprhistdate},'MM') < to_char({today},'MM')) AND (to_char({TODAY},'YY') = to_char({custrecord_deprhistdate},'YY')) THEN GREATEST(0, {custrecord_deprhistamount}) ELSE NULL END) ELSE NULL END

I think this can also be written as:

While you are simplifying the formula you can drop the

ELSE NULL

as well

also I just wanted to check @Marc that the date filter is supposed to be for 'year to last EOM', and not something like 'last rolling year'?

Thank you @David B @michoel and @Marc for all your support. It makes perfect sense. I just made it super complicated in my mind. Something I get myself confused is the ELSE NULL END. What's the general rule around it? If you have two or three WHEN, then you have to repeat END as per the number of CASE WHEN and WHEN?

Nesting case expressions is fine, although sometimes you can get away without doing it. What Michoel is saying is that

ELSE NULL

is redundant, as if you just omit the ELSE, you'll get a NULL anyway Have a look here, it shows the structure of a CASE expression well. You'll see that you need one END per CASE, but you can have as many "WHEN condition THEN _result_" as you like inside