23 Replies Latest reply: Mar 14, 2018 2:41 PM by Sunny Talwar RSS

    How to find Top N rows based on one dimension for specified duration?

    Sharath Kumar A

      Hi All,


      I want to find  Top N rows based on one dimension for specified duration, let me elaborate this;


      I want to find  sum of Field1 based on Field2 Top 10 records

      I used below expression;


      Sum({<Field2 = {"=Rank(Sum(Field1))<=10"}>} Field1)

      Its working fine and fetching top 10 records based on Field2 for entire duration in DB.


      Actually My requirement is I want to find Top 10 Records for particular duration (Say from '3/1/2017' to '/12/31/2018').


      To achieve this I have passed filter inside Rank function while finding sum as shown below;


      Sum({<Field2={"=Rank(Sum({<DateField={[>=$(=Date(varFrom)) <=$(=Date(varTo))]}>}Field1))<=10"}>}Field1)

      where varFrom= '03/01/2017' and varTo='12/31/2018' for one selected scenario.


      but did't worked  It always returns Top 10 rows for all the duration even we pass duration filter in the set analysis..


      So I request you can you Please suggest how to achieve my goal? i.e. find Top N records for based on one dimension for specified duration.


      Thanks in adavance..


      Waiting for reply...