Calculated Dimension not working

[#1]

Hi All,

I have a put in a calculated, to work out buckets, need to be dynamic so no script change. The expression is:

=IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

Worryingly it doesn't parse, but when placed as a normal expression it returns correctly just not grouping up.

Any help appreciated.

[#2]

oops missed out the most important word 'dimension' as in calculate dimension.

[sunny_talwar · Accepted solution]

You need the Aggr() function for the expression

=Aggr(

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

, Dimension/s)

Dimension/s would be the dimensions on which you are aggregating your ChangeIncShareUSD

[Anil_Babu_Samineni]

May be one extra comma is there?

=IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

  IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

[#5]

Thanks it is now working, just putting an Inline table to order the buckets. Thank you again.