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

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

Regards,

Sharath

• ###### 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

• ###### 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

• ###### 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

• ###### 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?

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

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

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

• ###### 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?

• ###### 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

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

I am not sure how you are doing it... but can you try this in a KPI object and share a screenshot of what you are seeing

='>=' & Date(varFrom) & '<=' & Date(varTo)

Does this give you the range you want and in the format of your date field? If it does, then ignore this next expression in KPI... else try this

='>=' & Date(\$(varFrom)) & '<=' & Date(\$(varTo))

Is this working? not use the one which works, like this

Sum({<Field2 = {"=Rank(Sum({<DateField={""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 10"}>}Field1)

or

Sum({<Field2 = {"=Rank(Sum({<DateField={""\$(='>=' & Date(\$(varFrom)) & '<=' & Date(\$(varTo)))""}>}Field1)) <= 10"}>}Field1)

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

Hi Sunny,

below is the screen shots of result in KPI when using '

='>=' & Date(varFrom) & '<=' & Date(varTo)

we are getting expected date range..

can you guide me where is the issue

sharath

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

Two things

1) This did not work?

Sum({<Field2 = {"=Rank(Sum({<DateField={""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 10"}>}Field1)

2) Your date field format is MM/DD/YYYY or M/D/YYYY? May be try this

Sum({<Field2 = {"=Rank(Sum({<DateField={""\$(='>=' & Date(varFrom, 'MM/DD/YYYY') & '<=' & Date(varTo, 'MM/DD/YYYY'))""}>}Field1)) <= 10"}>}Field1)

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

Thank you very much Sunny.. Now its returning top 10 records properly, but  returning wrong sum, i.e

sum of each row(Field1) is not correct, so overall sum is not correct.

Can you please look into this and suggest solution.

sharath

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

look into what my friend? all we have is description from you.... are you able to share a sample to look into?

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

Sorry Sunny, here is the details;

My expression:

Sum({<Field2 = {"=Rank(Sum({<DateField={""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 10"}>}Field1)

and results coming as shown below;

so here Zipcode (Field2) are coming properly (TOP 10) which is shown in GREEN color, but sum of Field1 are not correct (compared with query result) which is highlighted in RED color.

Sharath

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

Hahahaha, once again... you mentioned few things

1) query... where is that? what did you get from the query? Did you have other conditions?

2) Red portion is not right... what are the right numbers?

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

1. Query I used is :

SELECT top(5) [Field2]

,sum([Field1]) as [Field1]

FROM DB

where  (([Year]=2017 and [Month] in ('Jan','Feb','Mar','Apr','May','Jun')) )

and Field3 in ('XYZ')

group by [Field2]

order by [Field1] desc

and the Resultant result is given below;

I used the below expression now to find sum based on find Top 5 records as show below;

Sum({<Field2 = {"=Rank(Sum({<Field3={\$(varType)}, DateField={""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 10"}>}Field1)

where   1.  varType is variable having values 'XYZ'

2.  varFrom='01/01/2017'

3.  varTo= '06/30/2017'   which are passed as filter.

and below is the dashboard result;

Hope U understood the problem, Please guide me what and where I am making mistake.

Thank you so much for your help........

Regards,

Sharath

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

Try this

Sum({<Field2 = {"=Rank(Sum({<Field3 = {\$(varType)}, DateField = {""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 10"}, Field3 = {\$(varType)}, DateField = {"\$(='>=' & Date(varFrom) & '<=' & Date(varTo))"}>}Field1)

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

Thank you very much Sunny..

Actually I want TOP 5 records, so I modified expression as shown below;

Sum({<Field2 = {"=Rank(Sum({<Field3 = {\$(varType)}, DateField = {""\$(='>=' & Date(varFrom) & '<=' & Date(varTo))""}>}Field1)) <= 5"}, Field3 = {\$(varType)}, DateField = {"\$(='>=' & Date(varFrom) & '<=' & Date(varTo))"}>}Field1)

Its working in most of cases.but its fails in below mentioned scenario;

This is the Top 10 result for one duration in Query;

Here 3 rows (Highlighted in yellow color) in Field2 sharing same Rank (i.e. 5) because of this dashboard populating 7 rows in the result which is shown below;

This problem comes when 5th rank is shared by more than one rows(Filed2).

Since I have to use this expression inside different component (KPI, Chart, Map) I can't use dimension filter to restrict TOP 5 (Using Properties setting)

So my question is it possible to restrict only 5 rows even 5 th Rank is shared by multiple rows as shown in above pic.

Thank you.

Sharath

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

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)

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

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

sharath

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

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

.

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

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?

• ###### 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