7 Replies Latest reply on Apr 10, 2018 10:28 AM by Mike Mainzer

# Sum By Top Values in Groups

Hello,

Here is the basic problem. Assume the following simple table, which list the number of views of an article (K#).  I would like to sum by grouping the top 2 (10 + 9 = 19), the top 4 (10 + 9 + 8 + 7 = 34), the top 6 (10 + 9 + 8 + 7 + 6 + 5 = 45 and so on.

In my real world example, I have about 6800 articles inputted with associated article views and I would like to graph (bar chart) the number of views from the top 50 read articles, then the top 100 (which includes the top 50), and so on.

Any thoughts on the easiest way to do this?

KAViews
K110
K109
K38
K47
K86
K65
K54
K23
K72
K91
• ###### 1. Re: Sum By Top Values in Groups

Hi David,

Easiest way would be creating parameter and using it in a filter. Workbook v10.3 is attached.

~Tushar

• ###### 2. Re: Sum By Top Values in Groups

Hi Tushar

Thank you for replying, I truly appreciate it. I looked at the workbook and I think it is it not quite correct based on what my requirement. The workbook picks out and displays the top 4 (in the WB example) with the respective views (10, 8, 7, 9) in the example you provided.

However, what I was looking for is

Top 2 - 19 views ... in total by adding K1 views + K10 views

Top 4 -  34 views ... in total by summing the views for K1, K3, K4, K10

Top 6 - ... and so on

Then, ideally graph on a bar chart/line chart/etc the results of Top 2, Top 4, Top 6, etc.

• ###### 3. Re: Sum By Top Values in Groups

Hi David,

Do you want like this ?

Please find the solution workbook as well.

Thanks,

Ritesh

• ###### 4. Re: Sum By Top Values in Groups

Just to clarify.

You want two bars -

Articles that have the most through the 50th most views (top 50).

Articles that have the 51st through the 100th most views (51-100).

Or do you want that second bar to be the top 100, which would be counting 1-50 again?

• ###### 5. Re: Sum By Top Values in Groups

Simplest way is to turn the GRAND TOTAL ON

Thanks,

Ritesh

• ###### 6. Re: Sum By Top Values in Groups

Hi Ritesh,

Thanks for replying. Your suggestion actually is useful for a different view. However, what I would like to have is a graph of the running total - example below built in Excel. Basically Top 2 is the views for K1 & K10 summed. Top 4 is the views K1, K10, K3 & K4 summed and so forth.

In in my real world scenario, I would similarly sum the views of the top 50 articles (K1, K1501, K3, K4157, etc) together to get the totals views of all the Knowledge Articles. Top 100 would sum the total view of the Top 100 viewed articles which include the Top 50 referenced earlier. I will look at an earlier response as well. Thanks again for your help.

• ###### 7. Re: Sum By Top Values in Groups

IF RANK(SUM([Views])) <=2 THEN "Top 2"

ELSEIF RANK(SUM([Views])) <= 4 THEN "Top 4"

ELSEIF RANK(SUM([Views])) <= 6 THEN "Top 6"

ELSEIF RANK(SUM([Views])) <= 8 THEN "Top 8"

ELSE "Top 10"

END

Bring this calculated field to Columns

Bring [Views] to Rows

You should be good. I think the Rank calculation is ok.

1 of 1 people found this helpful