Capping Values

[#1]

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 ))

[swuehl]

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

[johnw · Accepted solution]

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.

[#4]

Thanks a lot both of you!