Set analysis-How to only show results greater than a certain value

[paul_ripley]

Hello

The set analysis below produces the table below.

I would only really like to show any portfolio where the amount is greater than 500,000 ie rounded to the nearest 1million,

I would like to do this in set analysis as I would need to reproduce in N Printing as a formula

sum({< proceeds = {"Proceeds"}>} [GPR GBP])*-1

Would anyone be able to help with this?

Many thanks

Paul

[Chanty4u]

may be this

sum({< proceeds = {">$(vLimit"}>} [GPR GBP])*-1

letting vLimit = 500000

[sunny_talwar]

500,000 is less than 1million... So, if we are rounding... anything more than 500,000 will be rounded to 1 million... so, essentially, anything above 500k and below 1mil... will show up as 1 mill?

May be this

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

[paul_ripley]

Hi Sunny

It kind of worked. My table now shows one line of 4m

But when I put it into a text box, which is ultimately what I need to do it gives me 5m?

[paul_ripley]

HI Chanty

Thanks for replying but that didn't work unfortunately

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, business_reclass, portfolio, portfolio_text))

[paul_ripley]

Hi Sunny

That gave a value of zero

Paul

[sunny_talwar]

May be the set analysis is missing from outer aggregation

Sum({<proceeds = {'Proceeds'}>}Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, business_reclass, portfolio, portfolio_text))

[sunny_talwar]

Also, I might have misstyped the name of your first dimension

Sum({<proceeds = {'Proceeds'}>}Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, bussline_reclass, portfolio, portfolio_text))

[paul_ripley]

Sorry, that still gives zero