Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
sunny_talwar

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

Anonymous
Not applicable
Author

Hi Ali,

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

sharath

Anonymous
Not applicable
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

Sharath