Skip to Content
0
Former Member
Jul 26, 2016 at 04:23 PM

Top 10 List, Remainder in "Others" group, and Graph

35 Views

(Please note, I have read though every top 10, etc post on SCN, none have been able to provide a solution with graph.)

Requirement

  1. Goal is to create a Top 11 Dimension list, with true top 10 of a Measure, as well as all remaining Dimension values grouped into an 11th item in the Top 11 titled “OTHER”.
  2. This list would be a summary table, displaying the Measure values as well as the summed Measure value for the combined OTHER group.
  3. This summary table would also be displayed in a graphical format, such as a pie chart per requirements.
  4. This report has prompts and will also be filterable at run time, meaning the Top 11 list is dynamic and unable to be created at the datasource layer.

I've been working this for weeks, let me try to explain a few avenues I have attempted, and then humbly request your help. I've attempted to simplify for post length, but please let me know if you need more information.

1) First try - Using Footer row in summary table and NoFilter() function to display "Others" group. The issue with this solution is that it is not able to be graphed as shown in the summary table. The graph itself breaks out all of the remaining Dimensions with their respective counts, as you can see it's a mess. (please ignore the error in the % of total column)

2) Second try - I first created the table used in the first screenshot below, and then attempted to flatten the dimension column to an "Others" group. In turn I would flatten the Count of the Others group into one sum, but I haven't been successful at flattening the dimension yet. The second screenshot demonstrates that even though I am replacing the Dimension with a variable to attempt to create an Others group, it still retains a row for each replacement. I attempted to only show the dimension with top 10 and the Other group on the far right, but you can see that doesn't even work.


Dimension Variable = If LineNumber() > 11 Then "OTHER" Else [NWA EIP 2 Ccat (Num)]

I'm really at a loss, every attempt seems to have some issue - these are only two of my most promising examples that I have been working with. Any suggestions or help is greatly appreciated, I'm not sure my keyboard will survive another day of my head banging on it.

Attachments

SCN_Post3.JPG (59.2 kB)
SCN_Post2.JPG (130.6 kB)
SCN_Post1.JPG (145.9 kB)