Top 10 + Rest

[colt]

Hi,

I need a report based on relational data to show list top 10 Articles + summarized Rest based on item [Revenue]=total([paid]).
Base query has items [Year], [Articlenumber],[Articlename],[Revenue].
I added [Rank]=rank([Revenue]).
This works fine so far, but when I try to replace [Articlenumber] and [Articlename] in list with items

[Number]= CASE WHEN [Rank] > 10
                            THEN 'Rest'
                            ELSE [Articlenumber]
                   END
[Name]=CASE WHEN [Rank] > 10
                       THEN 'Rest'
                       ELSE [Articlename]
              END

to only get top 10 ranked lines + line with summarized Rest
then things run out of control and only 1 line is displayed.

Any ideas how to get the right result ?

[blom0344]

Yes, I have a report that sports 20 graphs based on top x + rest , where the value of 10 is made dynamic by entering an x value through a text prompt.

The general idea:

Each list/graph runs on an union set:

Set 1: top x values based on a rank definition [Ranker]  Set a filter like : [ranker] <= 10 (with application : after auto aggregation)
Set 2: fetches the rest of the data based on  [ranker] > 10 (with application : after auto aggregation)

The trick is that you cannot refer to the ranked object directly, since the outcome depends on it (sort of circular reference)

By using a union set and filtering the sets AFTER the ranking is performed you will get the requested outcome..

[colt]

Dear blom0344 - thanks for your help.
I managed to get top 10 in the first query. I have also created the second query with all the entries rank > 10 - but how do I get them aggregated ? Remember the Rank is done after auto aggregation. Therefore I get multiple lines as result of query 2, but as result I just want one line: Year(filtered by prompt to 1 year), 'Rest', total

[blom0344]

The result of the second query should rollup to one line provided you do not add the rank itself to the contents or properties of the list..