Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I would like to print on a Text & Image "Chart" a top 5 values of a sum over some companies balances. I need to get both my top 5 companies names and the matching balances I want to display
Here is my expression for my top 1 company, this one works fine:
=Only(If(Aggr(Rank(Sum({$<[Account Number]={"467*"}>} Solde )),[Company Name])=1,[Company Name]))
But then, I don't know how to get the balance for a top company. The input to get the balances I want through ANY company, would be this one (it works):
= Sum( {$ <[Company Name] = {"467*"} >} Balance)
But this gets me as much values as i have companies, it does not work for my Text and Image chart... For example, I would like to print it just for my top 3 company according to the above expression. How can I do that ?
Thank you
Thank you ! You just confused Account Number and Company Name, but when I replaced it, it worked perfectly well. Here is the formula I used:
=Concat(Distinct (Aggr(If(Rank( Sum( {$ <[Account Number] = {"467*"} >} Balance)) <= 3, [Company Name]& ' ('& Sum( {$ <[Account Number] = {"467*"} >} Balance)&')') , [Company Name])), chr(13))
Hi
Try like below
=Concat(Distinct (Aggr(If(Rank( Sum( {$ <[Company Name] = {"467*"} >} Balance)) <= 3, [Company Name]& ' ('& Sum( {$ <[Company Name] = {"467*"} >} Balance)&')') , [Company Name])), chr(13))
Thank you ! You just confused Account Number and Company Name, but when I replaced it, it worked perfectly well. Here is the formula I used:
=Concat(Distinct (Aggr(If(Rank( Sum( {$ <[Account Number] = {"467*"} >} Balance)) <= 3, [Company Name]& ' ('& Sum( {$ <[Account Number] = {"467*"} >} Balance)&')') , [Company Name])), chr(13))