topic Incorrect Minimum Value in QlikView

Incorrect Minimum Value

ebc — Tue, 10 Mar 2015 13:58:00 GMT

In this scenario I have a table of many entries for a given entity (Brokers) with an associated value (Discount %). I am calculating the total Discount % for each Broker and want to find the min, max, and average of all Broker entities. Max and average appear to work but the minimum value always shows 0.0% which is incorrect.

Thoughts?

Thanks!

-Eric

Re: Incorrect Minimum Value

MK_QSL — Tue, 10 Mar 2015 14:04:08 GMT

=num(min(aggr(IF($(vDiscount)>0,$(vDiscount)),Broker)),'#0.0%')

Re: Incorrect Minimum Value

prieper — Tue, 10 Mar 2015 14:12:36 GMT

Please check the individual records, actually the result is 0, but hidden in the view.

Your variable "vDiscount" consists out of the fllwg formula (select 5%Mileage):

$(vCalc5LowMileage) / sum(counter).

The denominator has always a value, but the result of your variable "vCalc5LowMileage" is often "0", thus the resulting calculation is also "0" and the minimum value over all records is "0" as well.

So suggest to modify your expressions/variables

Edit: check a formula like

=num(IF($(vDiscount) <> 0, $(vDiscount)),'#0.0%')

HTH Peter

Re: Incorrect Minimum Value

ebc — Tue, 10 Mar 2015 15:02:49 GMT

This is interesting. Is the following a correct statement then?

Despite using the min function outside of the aggr function (as I do in the Text KPI object) the evaluation of minimum value actually interprets the calculation of every denominator (despite the conditional IF logic and the aggregated result).

e.g.

=num(min(aggr($(vDiscount),Broker)),'#0.0%')

vDiscount = IF(condition, sum(x)/sum(counter),

IF(condition2, sum(x2)/sum(counter) .....))

Many thanks!

-Eric

Re: Incorrect Minimum Value

prieper — Tue, 10 Mar 2015 15:16:14 GMT

Dear Eric,

It looks as if the entire calculation is made, before the instruction "Suppress Zero-Values" in the Presentation-tab is evaluated.

Probably the cracks will know it better, but find this phenomen quite interesting

Peter