topic Capping Values in QlikView

Capping Values

Tue, 15 Nov 2011 10:44:40 GMT

I wonder how you could cap values with set analys.

If you want to set values above 20000 Dollars, and cap those values to be 20000 Dollars and sum the rest as they are, how to write the logic for that.

In this case I am removing values below a certain limit. If I write 20000 as capped I will get the sum of 20000.

=if (sum(Money >= ($(Capped))), ($(Capped)), sum({$<Money = {'>=$(Limit)'}>} Money ) / sum( {$<CATEGORY = >} Days ))

Capping Values

swuehl — Tue, 15 Nov 2011 11:09:50 GMT

Hi,

first of all, I think you need to use double quotes for your search in the set element:

{$<Money = {">=$(Limit)"}>}

Then, I think your condition is not correct:

sum(Money >= ($(Capped)))

I am not sure what the result will be, but you probably won't limit the Money records with this, I think you need: sum(if(Money >= $(Capped),Money)).

Could you clarify at which aggregation level you want to sum Money and compare with $(Capped)?

Maybe a short sampe would help.

Regards,

Stefan

Re: Capping Values

johnw — Tue, 15 Nov 2011 23:56:45 GMT

I'm not sure I've understood, but maybe this?

sum(rangemin(20000,Money))

As Stefan indicates, though, you may want to sum Money at some level before comparing to the cap.

sum(aggr(rangemin(20000,sum(Money)),Dimensions)

It's rather unclear to me what you're after.

Re: Capping Values

Wed, 16 Nov 2011 07:54:49 GMT

Thanks a lot both of you!