Find the minimum value in a list of values in a field

[Anonymous]

Hi guys,

suppose i have a field with a list of values separated by comma like this:

30,-12,57,-90,98,10,4

is it possible to find the minimum value from the above list (they are all in the same string)

10x

[sunny_talwar]

May be like this:

RangeMin(30,-12,57,-90,98,10,4)

or

RangeMinString(30,-12,57,-90,98,10,4)

[sunny_talwar · Accepted solution]

For a field, it should be

=RangeMin(FieldName)

[Anonymous]

Thanks folks. Works fine.

[Anonymous]

Dear all,

Now it becomes  more complicated.

I need to calculate the average of the numbers in that string but only the average of the positive numbers.

is it possible?

thanks in advance/

[sunny_talwar]

May be try this:

=RangeAvg($(=Concat(If(SubField(FieldName, ',', ValueLoop(1, 100)) > 0, SubField(FieldName, ',', ValueLoop(1, 100))), ',')))

[Anonymous]

Dear Sunny, it works perfect.

just one question: why do you need the Concat?

Thanks

[sunny_talwar]

Long story short, I am checking each of the number using the SubField() function for the condition if it is positive or negative. If it is greater than 0 then keep it, else make it null and concatenate all positive numbers into a comma separated value so that I can use RangeAvg() on it. Does this make sense?

[Anonymous]

Yes. It is make sense. Beautiful explanation. Thanks.

[sunny_talwar]

Great

Mark any helpful responses so that others can find these responses easily when they land on this thread for answers.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny