Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
There might be no aggr() necessary. Just try it with:
min({<[Value]={">0"}>} total <Region> Value)
- Marcus
There might be no aggr() necessary. Just try it with:
min({<[Value]={">0"}>} total <Region> Value)
- Marcus
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
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
Thx