Sorting and Filtering of the list based on page selector.

Arnab116

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

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

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

Arnab116

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

Arnab116

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


 

Arnab116

Perfect!!!! Thanks for the Solution @ManjunathKN