Min() exclude 0 value at dimension level & return the relative dimension name

[Gavin_FBu]

Hi, 

We are new to Qlik Sense and struggled a few days with the follow questions.  Hope someone can shed us some light.

We have the following table with first 3 columns: 

Region	Location	Value	Try to return Lowest value at Region level (exc 0)	Try to return Lowest value's location at Region level (exc 0)
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 1:  
Try to return the lowest value within each region but exclude 0.
I can do "min( all aggr([Value], [Location]))" that return the lowest value overall and failed to do it at Region level.
Also {<[Value]={">0"}>} did not work.  Suspect all aggr overwrite the restriction but do not know how to resolve this.

Question 2:
Try to return Lowest value's location name at Region level.
Same as Question 1 but return the location name instead of value.  A few of our analysts failed to do this by  formula.  We suspect this may not be possible......

Please help.

[marcus_sommer · Accepted solution]

There might be no aggr() necessary. Just try it with:

min({<[Value]={">0"}>} total <Region> Value)

- Marcus

[Gavin_FBu]

Thanks Marcus.  I have separated Question 2 into a different post.

Quick question.  What happen if the Value actually is a calculated formula instead a static number.  I have a if function to calculate the value and when I put in the formula to replace Value, the formula is not work.  This is why we start from Aggr.

Cheers

[marcus_sommer]

Selections as well as set analysis conditions could be only applied against native fields and field-values and not against aggregation-results. This means additionally measures will be needed to resolve such a task.

Unfortunately there is no general way for it because it depends on the data + data-model + required views which ways would be possible and suitable and which rather not. Quite usually it's to query the condition within an if-loop, maybe something like this (simplified):

if(sum(Value) > X, min(Value))

or within a certain dimensional context:

min(aggr(if(sum(Value) > X, sum(Value)), Dim1, Dim2))

- Marcus

[Gavin_FBu]

Thx