Based on the formula above, the NVL({daysoverdue}, Round({today}-{trandate}, 0)):
NVL() ensures that if {daysoverdue} is null, it defaults to the calculated difference between {today} and {trandate}.
Round({today}-{trandate}, 0): Calculates the number of days since the transaction date.
This means the formula originally used the Days Overdue, which is based on the Due Date. Instead, we can modify it to replace {daysoverdue} with Round({today} – {trandate}, 0), ensuring it accurately calculates the number of days since the transaction date.
Here are the formulas you can copy and paste:
Formula for Current Open Balance:
Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) < 1 then {amountremaining} else 0 end
Formula for Open Balance = 30 days:
Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 1 and 30 then {amountremaining} else 0 end
Formula for Open Balance = 60 days
Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 31 and 60 then {amountremaining} else 0 end
Formula for Open Balance = 90 days
Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 61 and 90 then {amountremaining} else 0 end
Formula for Open Balance > 90 days
Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) > 90 then {amountremaining} else 0 end
Formula for Total Open Balance
(Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) < 1 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 1 and 30 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 31 and 60 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) between 61 and 90 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and Round({today} – {trandate}, 0) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and Round({today} – {trandate}, 0) > 90 then {amountremaining} else 0 end)
I would recommend creating a separate saved search for you to test the above formulas