Want to create a calculated item with last("X") / last("Y")
.
When Y = 0
, the result should be 0.
last("X") * (1 - count("Y",#1,0)) / (last("Y") + count("Y",#1,0))
For simplicity, let's write as:
x := last("X")
y := last("Y")
c := count(Y,#1,0)
The formula is now written
x * (1 - c) / (y + c)
c
is the key of this trick, which is 0 when Y != 0
and 1 when Y = 0
.
apply c = 1, y = 0
x * (1 - c) / (y + c)
=
x * (1 - 1) / (0 + 1) = 0
Zero division is avoided and the answer gets always 0.
apply c = 0
x * (1 - c) / (y + c)
=
x * (1 - 0) / (y + 0)
=
x / y
this is expected answer.