5 Replies Latest reply on Nov 27, 2018 11:35 PM by Nicole Cheong

MIN and MAX - not displaying correct results

Hi there....

Am trying to calculate the MIN and MAX values of the Tranche # based on a set of data sources. As you can see from the screenshot, the 1st initiative is the one with an issue while it is displaying the right result for the 2nd initiative. For the 1st, the MIN is 10 and MAX is 8. Seems like it is swapped. I have use a calculated field to do this and am puzzled as to how the results can differ if the same formula is applied across the board.

Anyone can assist? Much appreciated.

Formula for MIN:

{FIXED [Initiative Title]: MIN (if [Data Sources and/or Consumers]= "I" then [Planned Ingestion Tranche # as of Oct 2018] end)}

Formula for MAX:

{FIXED [Initiative Title]: MAX (if [Data Sources and/or Consumers]= "I" then [Planned Ingestion Tranche # as of Oct 2018] end)}

• 1. Re: MIN and MAX - not displaying correct results

Hi Nicole,

It appears that the Planned Ingestion Tranche # field is a string (I see the value "3b"). As a string, your numbers are sorted alphabetically when evaluating for Min and Max. The "1" of "10" will make it sort before "8", so the min of those two strings is 10.

Best,

Jennifer

1 of 1 people found this helpful
• 2. Re: MIN and MAX - not displaying correct results

This is happening because Planned Ingestion Tranche field is a string. You need to convert in to integer to find the correct max and min values.

Formula for MIN:

{FIXED [Initiative Title]: MIN (if [Data Sources and/or Consumers]= "I" then INT([Planned Ingestion Tranche # as of Oct 2018]) end)}

Formula for MAX:

{FIXED [Initiative Title]: MAX (if [Data Sources and/or Consumers]= "I" then INT([Planned Ingestion Tranche # as of Oct 2018]) end)}

Hope this helps.

1 of 1 people found this helpful
• 3. Re: MIN and MAX - not displaying correct results

Thanks Jennifer.

Will keep that in mind. I guess because it worked for others where there is no 1 and 10 together, I thought something must be amiss.

• 4. Re: MIN and MAX - not displaying correct results

Thanks Ombir for the explanation. I have changed it to a number and it is now working