Calculation of 10 Clients

[tmumaw]

Hi All,

Need a little help with a statement.  I'm trying to get my top 10 clients YTD in a text box.  The only part I'm having a hard time with is figuring out where to put the second statement (FISYR and YTD statements).  The top statement works fine.

Thanks.

=Num(SUM({<[Client Name]={"=sum([Inv Gross])>=$(=max(aggr(sum([Inv Gross]),[Client Name]),10))"}>}[Inv Gross]),'$###,###,##0' )
({<FISYR = {$(=$(vFisYr) - 1)},YTDFlag = {1}>}

[sunny_talwar · Accepted solution]

May be one of these

=Num(Sum({<[Client Name] = {"=Sum([Inv Gross])>=$(=Max(Aggr(Sum([Inv Gross]), [Client Name]),10))"}, FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>} [Inv Gross]), '$###,###,##0')

or

=Num(Sum({<[Client Name] = {"=Sum({<FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>}[Inv Gross])>=$(=Max(Aggr(Sum({<FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>}[Inv Gross]), [Client Name]),10))"}, FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>} [Inv Gross]), '$###,###,##0')

 or you can simplify them to this

=Num(Sum({<[Client Name] = {"=Rank(Sum({<FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>}[Inv Gross]) < 11"}, FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>} [Inv Gross]), '$###,###,##0')

or this

=Num(Sum({<[Client Name] = {"=Rank(Sum([Inv Gross]) < 11"}, FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>} [Inv Gross]), '$###,###,##0')

[tmumaw]

Thanks Sunny it worked perfect.