Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sharathkumara
New Contributor II

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

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

24 Replies
ali_hijazi
Honored Contributor

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

I suggest putting the date interval in the main set analysis sum({<date={[>=$(vfrom) <=$(vTo)]>},field2={=rank....

because you want to get the sales between 2 dates then whose rank is <=10

MVP
MVP

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

May be create a chart with Field2 as dimension and =Rank(Sum({<DateField={[>=$(=Date(varFrom)) <=$(=Date(varTo))]}>}Field1)) as expression to see if it is working the way you want or not. It is always a good idea to break down your expression to see what it is doing

sharathkumara
New Contributor II

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

Hi Ali,

I tried that way, but its returns top 10 record based on field2 for entire duration not for selected period.

sharath

sharathkumara
New Contributor II

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

Hi Sunny,

thanks for reply, I tried as you suggested, but it returning Field2 are not all correct. i.e. in the query I checked in DB, some are matching to dimension some are not matching.

What I feel, date range is not working inside rank function. Rank is assigned for entire data.

Please suggest any other options are there to achieve the target..

Thanks

Sharath

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

Can you share screenshot which uu reply to sunny? Even esc function key can also work

MVP
MVP

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

So, then you need to fix your set analysis basically.... without having too much info it might be difficult to help... would you be able to share this info

1) What is your datefield format?

2) What is the definition for varFrom and varTo

Also, look here for tips on Dates in Set Analysis

sharathkumara
New Contributor II

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

Thanks for reply Sunny,

Below are the details;

1.  I am using MM/DD/YYYY for EG: 07/01/2017

2. varFrom is FROM date value  and  varTo is TO date value eg: from Jan -2017 to Dec 2017 its value willl be as follows;

varFrom =01/01/2017 a  and  varTo = 12/31/2017

Thank you.

Sharath

MVP
MVP

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

varFrom and varTo are static values? they are not an expression?

sharathkumara
New Contributor II

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

No Sunny,  Based on date selection respective values come, its dynamic.

Sharath