Sorting and Filtering of the list based on page selector.

Hi Team,

I have a module with Location as a dimension and line items Sales, Profit, Expenses, Opex and Margin. I want to create a Dashboard or Dynamic Report where these line item will be Page Selectors and the locations will be published based on the selection , sequenced and filtered with TOP 5 values.

For Example :

Scenario 1-

If I select Sales , the Grid should show only TOP 5 Locations based on the sales.

Sales
Loc 1	1000000
Loc 4	900000
Loc 8	800000
Loc 11	700000
Loc 6	600000

Scenario 2-

If I select Profit , the Grid should reorder and should display only TOP 5 Locations based on the profit.

Profit
Loc 8	100000
Loc 3	90000
Loc 6	80000
Loc 1	70000
Loc 11	60000

Thanks,

Arnab

Accepted answers

ManjunathKN

Hi Arnab,

Lets Say you have the module with Location as list and Profit,sales etc as line items as below

Create a LIS and mark all the line items that you want to apply sort and filter.

And Create a module to achieve the sort and filter top 5.

and use the below formula

Note: item formula for LIS needs to be typed.
Apply the sort like below

Apply the filter like below.

Thanks,
Manjunath

All comments

bhatjaved

@Arnab116

Give a try with the help of line items subset where you can use current module line items as dimension in another module , and then they could be used as page selectors

ManjunathKN

Hi,

Go and apply the following in sequence Module-->select Data line item-->Data option(drop down in toolbar)-->sort-->Sort by Ascending. this will help you to get the desired result(for sorting)

And add one more line item(Name: Rank order) in your module and use the formula RANK(your data, Descending, Sequential,True, line item that includes list items) and Apply filter as Rank order < 6.

Thanks,
Manjunath

ManjunathKN

Hi

Below SS might help you,

Thanks,
Manjunath

ArnabG

Thanks for replying. In your solution you are creating a List(Page selector) and using it which is fine but I mentioned that these Page selectors (Sales,Profit,Expenses, etc.) are my line items in a module. So, how can I bring that data in your build module?

Thanks,

Arnab

ArnabG

I tried this and published them as page selector but main issue which I am facing is sorting and filtering the Top 5 values only. I tried using Rank function but it didn't work for a particular item(Sales,Profit,Expense,etc).

Thanks,

Arnab

ManjunathKN

Hi

You cannot bring the data (100000,90000, etc) in columns when you are using other line items as page selectors, unless you are using it as LIS.

Thanks
Manjunath

ManjunathKN

Hi

if you already have it dashboard like you mentioned.
for sorting you can use the above way, but for filtering you can still use one rank formula without grouping.
that is Rank(your line item) for each line item and you can create final filter module using or logic for each rank formula line item, apply it as final filter > 6(this does not work for LIS)

Hope this helps.

Thanks
Manjunath

ManjunathKN

Hi Arnab,

Lets Say you have the module with Location as list and Profit,sales etc as line items as below

Create a LIS and mark all the line items that you want to apply sort and filter.

And Create a module to achieve the sort and filter top 5.

and use the below formula

Note: item formula for LIS needs to be typed.
Apply the sort like below

Apply the filter like below.

Thanks,
Manjunath

ArnabG

Perfect!!!! Thanks for the Solution @ManjunathKN

Quick Links

Can you help?

Unanswered questions

New UX Filtering for Hierarchy sibling
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…
A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…