Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
may be this
sum({< proceeds = {">$(vLimit"}>} [GPR GBP])*-1
letting vLimit = 500000
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))
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?
HI Chanty
Thanks for replying but that didn't work unfortunately
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))
Hi Sunny
That gave a value of zero
Paul
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))
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))
Sorry, that still gives zero