Top and Bottom N filtering

Katie_Easton
10 days ago
3replies
Answered

Hi,

I want to be able to filter my matrix to show both the top 1 and bottom 1 value in the same table. Is there a way to do this? Currently I can only see that its possible to do one or the other using top N and bottom N but not have both in the same table. The in-between filter doesn't give the desired outcome as you can only filter on actual values.

Thanks

filtering

- imbarmarinescubar_pyram
- 9 days ago
- Reported - view
Hi ,

You cannot currently do this using the filter wizard (but you will be able too in a future version).

Instead you'll need to create a custom list. The idea is to create a custom list that is built off 2 other custom lists, one for the top and one for the bottom.

You can do this in 1 go, using scripting, by writing:

{Top([Table].[Column].allmembers, 1), Bottom([Table].[Column].allmembers, 1)}

Using these functions:
https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Formulate/Lists/FilterFunctions/Bottom_Count.htm
https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Formulate/Lists/FilterFunctions/TopCount.htm

Or by creating 3 lists using the graphical editor:
https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Formulate/Lists/Lists.htm

Imbar
- Katie_Easton
- 9 days ago
- Reported - view
Hi, thanks for the above.

I have an added complexity to the query i need to pull as the top 10 is based on the first column and then the top 1 and bottom 1 need to be based on the ppt change.

I have made two scripts one to pull the top 10

And then the next one to get the top and bottom from the made list

However when i apply it, it doesn't pull through the correct elements based on the data i have in my discovery.

Is there a different solution to this?
- imbarmarinescubar_pyram
  
  9 days ago
  
  Reported - view
  You need to add to the filters the "column" you want to filter by.
  So the top 10 should have ([measures].[+b88...+], [When - Quarter].[When - Quarter].@[PARAM_ID]) - with the parameter using in the discovery (I'm assuming it's a parameter since I can see the slicer above the grid).
  
  For the top/bottom 1 I see you already use the pptChange (correct? I can only see it is a calculation).
  Additionally, add the measure that is used in the report. I now see you are using an MDX data source, which, if you do not specify the measure it will used the default measure for the cube.
  So the 3rd parameter for both the top and the bottom should be
  ([measures].[+b88...+], [When - Quarter].[When - Quarter].[+14....+]) instead of just [When - Quarter].[When - Quarter].[+14....+] as you had it.