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 11000000
Loc 4900000
Loc 8800000
Loc 11700000
Loc 6600000

 

Scenario 2-

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

Profit 
Loc 8100000
Loc 390000
Loc 680000
Loc 170000
Loc 1160000

 

Thanks,

Arnab

Best Answer

  • Hi Arnab,

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

    ManjunathKN123_0-1643221147141.png

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

    ManjunathKN123_1-1643221210406.png

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

    ManjunathKN123_2-1643221289097.png

    and use the below formula

    ManjunathKN123_3-1643221327382.png

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

    ManjunathKN123_4-1643221391692.png

     

    Apply the filter like below.

    ManjunathKN123_5-1643221431833.png


    Thanks,
    Manjunath



Answers

  • @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 

  • 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

  • Hi

    Below SS might help you,

    ManjunathKN123_0-1643078665951.pngManjunathKN123_1-1643078692408.pngManjunathKN123_2-1643078737267.png

    Thanks,
    Manjunath

  • 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

  • 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

  • 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

  • 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


     

  • Perfect!!!! Thanks for the Solution @ManjunathKN