Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I am going to guide you in a direction... read here and you might be able to get this fixed all by yourself
Thank You Sunny... I will check this and give updates to you..
sharath
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
.
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?
Facing the same Issue, please if anyone has come up with the solution please reply..