Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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}>}
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')
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')
Thanks Sunny it worked perfect.