Expression set analysis and renaming values

[paul_ripley]

Hello

The set analysis below, will bring back results for any portfolio where the GBP proce eds is > 500,000 (excluding portfolio 22701282).

I would like to amend it slightly, so that the below is still correct but with the additional criteria that any portfolio with proceeds < 500,000 is also included but they are grouped together and renamed as 'Other'

Sum(Aggr(

If(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1, 1E6))

, bussline_reclass, portfolio, portfolio_text))/1000000

For example below currently below I would only return Prisa and SLR.  I would like to group the rest to be called 'Other' as they are less than 500,000

Could anyone help with this?

Many thanks

Paul

[sunny_talwar]

Checking

[paul_ripley]

Hi Rick

Unfortunately dimension limits didnt work because I had 2 expressions in the table and the portfolio could be zero in 1 expression but > 500,000 in the other

I tried using your code in the expression.  The current expression is

=(sum({<entity -= {"0148"},pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282} > } [GPR GBP])*-1)

So I entered the below but it seemed to have an issue with 'OTHERS'.  Can you see what ive done wrong?

=IF( AGGR(


(sum(
{
<entity -= {"0148"},
pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282}
>
}
[GPR GBP])*-1)


, portfolio) <= 500,000 , portfolio ,'OTHERS')

[paul_ripley]

Hi Sunny

Did you get chance to take a further look?

Thanks

Paul

[rick_vansoest · Accepted solution]

It had some trouble with the , in the 500,000

Replaced the old dimensions with the new ones:

This calculation is working for me:

=IF( AGGR(

(sum({<entity -= {"0148"},pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282} > } [GPR GBP])*-1)

, portfolio) >= 500000 , portfolio, 'Others')

you could do the same for portfolio_text and Money Multiple (GBP) Depending on the requirements.

Final result:

[rick_vansoest]

Dimensions limits gave me the same result. Just make sure you set the expression which you want to use first.

[paul_ripley]

Hi Rick

Thanks very much for your help.  It is now working

Kind regards

Paul