BusinessObjects Board

Webi Formula Help with Greatest/Most Recent Date

I have 3 date dimensions in a webi report. I wish to return the most recent date, but also take into account some dates could be null/blank. I’ve got this far but i know im missing so obvious outcomes eg if Date1 & Date 2 are null then it should return Date3

=If(([Date1]>[Date2] Or IsNull([Date2])) Then [Date1] Else If([Date2]>[Date1] Or IsNull([Date1])) Then [Date2] Else [Date3])

Am i going down the right path with this or is there a better way? TIA

=If(IsNull([Date1]) And IsNull([Date2])) Then [Date3] Else If([Date1]>[Date2] Or IsNull([Date2])) Then [Date1] Else If([Date2]>[Date1] Or IsNull([Date1])) Then [Date2] Else If([Date3]>[Date1]) Then [Date3] Else If([Date3]>[Date2]) Then [Date3]

Does this cover it?

You need to account for all three dates when comparing. If Date3 is the latest date, as is, your code won’t return it, you’ll get the largest of Date1 or Date2 even before looking at Date3.

What if all three dates are null? If you just want to display Date3 (null), then you are technically covered (though I would prefer to see it spelled out so future developers clearly see the intent).

Instead of trying to do the null logic and comparison logic all together, I’d split it up. Create a variable for each date that handles the nulls. Then create the comparison using the variables.

Noticed you are using > operators instead of >=. If Date1 = Date2, you’ll result won’t be returned properly.

Hierarchical if then statements typically use “ElseIf” vs “Else If”. Just something to watch out for, especially if your statements get more complex with layers of parenthesis involved.

Here’s a quick stab at it. May need some tweaking.

–set the nulls to 1/1/1800 (or some early date) so comparisons will work
v_D1: = if isnull( [Date1] ) then todate(“01/01/1800” ; “MM/dd/yyyy”) else [Date1]
v_D2: = if isnull( [Date2] ) then todate(“01/01/1800” ; “MM/dd/yyyy”) else [Date2]
v_D3: = if isnull( [Date3] ) then todate(“01/01/1800” ; “MM/dd/yyyy”) else [Date3]

–get the largest date
v_D:
= if [v_D1] >= [v_D2] and [v_D1] >= [v_D3] then [v_D1]
elseif [v_D2] >= [v_D3] then [v_D2]
elseif [v_D3]

–set the 1/1/1800 back to null
vD_Final:
= if [v_D] > todate(“01/01/1800” ; “MM/dd/yyyy”) then [v_D]

1 Like

Thankfully Date1 will never be null and we have measures in place to ensure Date1 will never be greater than Date2 or Date3 within the actual data itself.

Ive got with the following on this basis and following your recommendations…

v_D1: =If(IsNull(Date2])) Then [Date3] Else [Date2]
v_D2: =If(IsNull([Date3])) Then [Date2] Else [Date3]

v_D: =If(IsNull([v_D1]) And IsNull([v_D2])) Then [Date1] Else If([v_D1]>=[v_D2]) Then [v_D1] Else If([v_D2]>=[v_D1]) Then [v_D2]