Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a requirement where I need to show Top10 Field1 and then show the coverage percent that the Top10 represents relative to the total, ie, imagining the Dimension Limits tab where we can choose to Show Only Values that accumulate to x% relative to the total - in this case I need to figure out what that percentage is.
I've made the Top10 by building a Straight table, forcing its sort order and then simply limiting the number of items presented (in the Presentation tab), so I used no Dimension Limits.
The percent coverage is supposed to be shown in a text box or even table caption.
I've been browsing through F1 as well a the community and I was thinking about using aggr and rank but I still haven't found the right way to do this - do you have any ideias on this?
Thank you very much,
Marina C.
This looks roughly like the right approach:
if( aggr(rank( Sum(Value) ),Field1) <=10, Sum(Value)) / Sum(Value)
Here's what I think would work:
=sum(if(aggr(Rank(sum(Value)),Field1)<=10,aggr(sum(Value),Field1)))
/
sum(Value)
There may be cleaner ways to do this, but near as I can tell, this will work.
Can you provide sample data or apps?
Hi Manish,
Thanks for the reply. I can't provide apps so I'll try to explain it better:
If this is my Top10 Field1 Value table, what is the percent coverage that the sum of the values represent?
# | Field1 | Value |
1 | H | 1500 |
2 | I | 1400 |
3 | J | 1300 |
4 | D | 1200 |
5 | E | 1100 |
6 | F | 1000 |
7 | G | 999 |
8 | A | 900 |
9 | B | 850 |
10 | C | 500 |
The sum of these values would be (1.500 + 1.400 + 1.300 + 1.200 + 1.100 + 1.000 + 999 + 900 + 850 + 500) = 10.749
If the Total Value (of all Field1, not just the ones in the Top10) is 107.490, then the Top10 would represent 10% (10.749 / 107.490 = 0,10 )
So I was thinking about using the Rank function to achieve this in a text box with something like:
if( aggr(rank( Sum(Value) ),Field1) <=10, Sum(Value)) / Sum(Value)
Use below expression
SUM(Value) and tick Relative under expression tab
Or use as below
SUM(Value) / SUM( ALL Value)
Hi Manish,
Thanks but that isn't it.
Maybe I didn't explain it well: I need to present the percentage value in a text box. I need to sum the values of the top10 and then divide them by all of the values and I need to do that outside of the straight table.
Best Regards,
Marina C.
And I wouldn't want to use ALL because I do want current selections
This looks roughly like the right approach:
if( aggr(rank( Sum(Value) ),Field1) <=10, Sum(Value)) / Sum(Value)
Here's what I think would work:
=sum(if(aggr(Rank(sum(Value)),Field1)<=10,aggr(sum(Value),Field1)))
/
sum(Value)
There may be cleaner ways to do this, but near as I can tell, this will work.
Hi Marina,
if you need to do this in a text box, you can use the below, use set analysis within your first sum to just get the top 10 rank, then divide over total
=Sum({<Field1={"=rank(sum(Value))<= 10"}>} Value) / Sum(Value)
hope that helps
thanks
Joe
Joseph,
Thank you very very much. It works perfectly!
Couldn't mark your answer as correct, as orsh_ has also given me a correct answer, but it is perfectly correct.
Thank you again,
Best Regards,
Marina C.
ha no worries, he was 2 mins faster after all
glad to be of help