cancel
Showing results for 
Search instead for 
Did you mean: 

If value = Null then apply next Max value

Former Member
0 Kudos

Hi All,

I wonder if anyone can help with a formula I have been working on.

I am reporting on the duration between a case being logged and resolved.  If this data is recorded outwith our work hours then the value is null and we have set null to = 'NA' in the report.

I am reporting the Min, Max, Median and Average of these durations.  However, if any of the values = NA then this is the  Max value that is returned.  How do I get crystal to bring back the real Max value.

I tried using an If Then Else statement that returns the Nth largest - 2, 3, 4, 5, 6 (hoping there wouldn't be more than 6 NA values at any given time). However, this doesn't work - value NA is still returned.

I wonder if there is an easier way to handle this issue?

Thanks.

A.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi A,

You'd need to convert the field to 'number' first.

Create a formula with this code:

if IsNumeric({string field}) then

tonumber({string field})

While inside the formula editor, look for an option on the top that says 'Exceptions for Nulls', change that to 'Default values for Nulls'.

You can then setup all summaries to use the formula field instead.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thanks for your reply.

I am able to use the current field as it is to successfully generate a Max value as shown in attached.

Its only when the values for that field contain a 'NA' value that this gets set as the Max.

I need it to generate the next Max value after NA.

A.

abhilash_kumar
Active Contributor
0 Kudos

'NA' will always be the Max value because it is a string field that has numbers as well.

You'd need to 'remove' the 'N/A' value and convert the field to number as suggested above to get the correct Max value.

-Abhilash

Former Member
0 Kudos

Thank you!  I was using a formula field that had converted this value to a string.  When I used the numeric formula field I was able to get the correct Max value!

Many thanks!

A.

Answers (0)