Dynamic top N and others selection in OAC / OAS Self Service (DV)

We needed to create a dynamic selection of Top N in Oracle Analytics Project/Workbook.

The basics is using the blog Oracle Underground BI & Dataviz, specifically How to Calculate Top N Versus Rest of Population in Oracle Analytics  post and combine it with the variable prompt plugin. This plugin available for download here, and described in this video.

If needed, first I download the Presentation Variable prompt plugin (here) an install it in Catalog/Extensions/Upload Extension (just drop the zip file)

 

Next, I created the following:

Top 4 of "Product Containers"  by "# of Orders" and the rest as Others:

case when ATTRIBUTE(# of Orders) by Product Container)<4 then Product Container else 'Others' end 

 (If you don't like my logic, you can change it to <=4 and not <4)

 

as described in the above mentioned post.

This calculation should pass the validation.

Next we change it to work with the prompt plugin (I named it topn):

case when ATTRIBUTE(# of Orders) by Product Container)<@{topn}{4} then Product Container else 'Others' end 

Now it wouldn't pass validation. Disregard it.

 

Please note that this order is important, first the change of calculation, next add the UI.

In the Variable prompt properties I'll add the name and values.

And we are done.

We can make it look  a little better: