topic Re: Calculation of 10 Clients in QlikView

Calculation of 10 Clients

tmumaw — Sat, 16 Nov 2024 04:54:53 GMT

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}>}

Re: Calculation of 10 Clients

sunny_talwar — Thu, 20 Dec 2018 19:43:46 GMT

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')

=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')

Re: Calculation of 10 Clients

tmumaw — Thu, 20 Dec 2018 19:59:55 GMT

Thanks Sunny it worked perfect.