how to show top 1 , above 2 and below 2 rankings based on user selection in table

[ramrongala]

Hi Experts,

Requirement : user select any product then based on ranking it should show before 2 , after 2 rankings and top 1 ranking should as default(please have a look below  output screenshot)  

Below is the sample  data

need to show output as below :

Please help to resolve the isuue.

[PrashantSangle]

How much complex your data model? 

are you looking for something like this

Find below Script

Test:
LOAD Product,
Sales
FROM
Ranking.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Test1:
Load *,RowNo() as rank
Resident Test
Order By Sales desc;

drop Table Test;
EXIT SCRIPT;

In front end

chart > table

Dimension : rank, Product

Measure: Sum({<rank={1}>+<rank={">=$(=max(rank)-2)<=$(=max(rank)+2)"}>}Sales)

Thanks & Regards,

Prashant Sangle

[ramrongala]

HI Prashant,

We need the ranking  calculation in frontend and user select one product not ranking.

[ramrongala]

HI Prashant,

requirement got changed, now we have to show +2 and -2 sales based on selection like below.

sample data:

 output should be: +2 , -2 sales  and top sales(here user select bikehelmet)

Product	Sum(Sales)
Cars	32000
Wheels	9800
SpareParts	6000
Bike helmet	4000
Car speakers	3700
Bike	2600