Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate percent coverage of rows in straight table

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.

1 Solution

Accepted Solutions
Or
MVP
MVP

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.

View solution in original post

10 Replies
MK_QSL
MVP
MVP

Can you provide sample data or apps?

Not applicable
Author

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)

MK_QSL
MVP
MVP

Use below expression

SUM(Value) and tick Relative under expression tab


Or use as below

SUM(Value) / SUM( ALL Value)




Not applicable
Author

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.

Not applicable
Author

And I wouldn't want to use ALL because I do want current selections

Or
MVP
MVP

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

ha no worries, he was 2 mins faster after all

glad to be of help