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

[Anonymous]

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...

Regards,

Sharath

[sunny_talwar]

I am going to guide you in a direction... read here and you might be able to get this fixed all by yourself

Continuous Ranking (no missing Rank)

[Anonymous]

Thank You Sunny... I will check this and give updates to you..

sharath

[Anonymous]

Thank you sunny... I got the idea of how to use parenthesized Rank.

At the last can you clarify one of my doubt, below are the details;

The expression you suggested working almost fine, The only problem I am facing is;

In my sheet I am using  "SimpleFieldSelect"  extension (a single select filter ) which is used for filter out one of field say FieldFilter. based on this filter selection other components (KPI, Chart and Map ) are responding properly with correct value even I am not passing this filter filed as parameter in set analysis (similar to default filter).

Graph which finds Top 5 Zip codes (FIled2)  are also responding for fieldFIlter selection but values are not proper (i.e.All Top 5 Zip codes are not correct). so can you guide me How to pass this filter  (FieldFilter) as a part of set analysis while finding TOP 5 zip code based sum.

Thank you veryyy much....

Regards,

Sharath

.

[sunny_talwar]

It will be difficult for me to see why it doesn't work in a very specific object... may be use that same expression in a table object and see if it still have the same problem? I don't see any set analysis to ignore selection in FieldFilter field... so I would expect the expression to be based on the selection made... having said that I have never used the extension you mentioned and don't really know how it works?

[rajkumart]

Facing the same Issue, please if anyone has come up with the solution please reply..