Min() value but return the relative dimension name instead of value

[Gavin_FBu]

Hi, 

We are new to Qlik Sense and struggled a few days and hope you can shed us some light on the follow question.

We have the following table with first 4 columns: 

Region	Location	Value	min() at Region level (exc 0)	Same as pervious formula but try to return min()'s Location name instead of value
Auckland	WESTGATE	2.88	1.59	PAKURANGA
Auckland	PAKURANGA	1.59	1.59	PAKURANGA
Auckland	PUKEKOHE	0.00	1.59	PAKURANGA
Christchurch	KAIAPOI	4.12	2.28	CRANFORD ST
Christchurch	CRANFORD ST	2.28	2.28	CRANFORD ST
Christchurch	HORNBY	0.00	2.28	CRANFORD ST
Lower North Island	WHANGANUI	5.84	4.33	WAIRARAPA
Lower North Island	PORIRUA	4.36	4.33	WAIRARAPA
Lower North Island	WAIRARAPA	4.33	4.33	WAIRARAPA
South Island	ALEXANDRA	2.66	2.08	INVERCARGILL
South Island	WANAKA	2.14	2.08	INVERCARGILL
South Island	INVERCARGILL	2.08	2.08	INVERCARGILL

Question:

Column 4 contained a min() formula = min({<[Value]={">0"}>} total <Region> Value) that return min value at each Region.
We try to do the same in Column 5 but return the Location Name (base on the same logic) instead of Value.
A few of our analysts failed to do this by  formula.  We suspect this may not be possible......

Please help.

[rwunderlich · Accepted solution]

What you are looking for is:

=FirstSortedValue({<[Value]={">0"}>}total <Region> Location, Value) 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Gavin_FBu]

Hi Rob,

It is working when Value is a static number however if the Value actually is a 'if' formula., its broke.

I tried to put the formula in variable and use $(variable), it still failed.

Do you know why?

Cheers

Gavin

[rwunderlich]

Hi Gavin,

Can you post your expression?

-Rob

[Gavin_FBu]

Hi,

It is a IF function that based on a selected time range to do calculation.

Hope this help.

Cheers

Gav