2 Replies Latest reply on Aug 31, 2017 4:08 AM by T G

Top N and Bottom N with a single parameter Value

Hi All

I am creating a dashboard where I have a sort by and filter by parameter to select couple of fields to sort and filter the values accordingly.I cannot share the workbook how ever sharing a screenshot of What I am trying to achieve. So I have a set for Top N based on a parameter and

my view by formula is

case [Filter]

WHEN "Forecast Sum" then sum([Forecast_sum_Cogs])

when "PY" then ([Delta PY Cogs_acc])

when "PQ" then ([Delta CY PQ Cogs acc])

WHEN 'CQ' THEN SUM([CQ_cogs ])

END

Sorting formula is

CASE [Sort By]

WHEN 'Forecast Sum ▲' THEN SUM([Forecast_sum_Cogs])  //-High to Low

WHEN 'Forecast Sum ▼' then -sum([Forecast_sum_Cogs])  //Low to high

WHEN 'CQ ▲' THEN SUM([CQ_cogs ])

WHEN 'CQ ▼' THEN -SUM([CQ_cogs ])

WHEN 'PY ▲' THEN ([Delta PY Cogs])

WHEN 'PY ▼' THEN -([Delta PY Cogs])

WHEN 'PQ ▲' THEN ([Delta CY PQ Cogs])

WHEN 'PQ ▼' THEN -([Delta CY PQ Cogs])

END

The sort works but can I get bottom N of my dimension if I am writing a similar formula like this for Filter or is there any other way?.(I tried but its not working).

My requirement is to select Top N parameter values for eg :- 10 and when I select Forecast Sum ▲ I need to get Top N of my dimension based on  SUM([Forecast_sum_Cogs])  and

when I select Forecast Sum ▼  I need to get Bottom N of my dimension based on  SUM([Forecast_sum_Cogs])

Can someone help .

Regards,

Haya

• 1. Re: Top N and Bottom N with a single parameter Value

Any help would be much appreciated

• 2. Re: Top N and Bottom N with a single parameter Value

Refer the existing thread.

TG