BusinessObjects Board

Find minimum value in multiple column

building reports webintelligence
#1

I am trying to find the minimum value across three columns. I figure this could be found using a simple if statement however I am having issues with the null values that may be in some of the columns. here is an example of how my table is set up in webi. The last column is my calculated column which needs to be corrected to account for null values

Item# | Days Aged | Days to Submit | Days to Escalate | MINIMUM
  1            271              11                      235                   11
  2            322              NULL                  59                     59
  3            148              86                      NULL                ERROR
  4            58                NULL                  NULL                ERROR

This is my variable:

=If([Days Aged]<[Days to Submit]) Then(If([Days Aged]<[Days Aged Escalate]) Then [Days Aged] ElseIf( [Days Aged Escalate]<[Days to Submit]) Then [Days Aged Escalate]) ElseIf([Days to Submit]<[Days Aged Escalate]) Then [Days to Submit] Else [Days Aged Escalate]

Im Missing something but cant figure out what it is. Thanks!!

[Moderator Edit: Added code formatting - Andreas]

bayliss59101 (BOB member since 2018-01-02)

Find minimum from columns
#2

The IsNull() function would be useful here.

Nick Daniels :uk: (BOB member since 2002-08-15)

#3

yes I started to write using the IsNull() but I couldn’t seem to find the right combination to get it to work. I was hoping someone could provide me with a code example.

bayliss59101 (BOB member since 2018-01-02)

#4

One approach would be to define three new variables that are (almost) guaranteed non-null, and use them in your comparison instead of the original values.

[DA] = if isnull( [Days Aged] then if( isnull( [Days to Submit]; [Days to Escalate]; [Days to Submit]) else [Days Aged]
[DTS] = if isnull( [Days to Submit]) then if( isnull( [Days Aged]; [Days to Escalate]; [Days Aged]) else [Days to Submit]
[DTE] similarly

Bill K (BOB member since 2011-02-24)

#5

This worked. Thanks so much!

bayliss59101 (BOB member since 2018-01-02)

#6

:slight_smile:
Thanks for letting us know :slight_smile:

Bill K (BOB member since 2011-02-24)