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

    Top N and Bottom N with a single parameter Value

    Haya mohiyaddin

      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