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

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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.

waiting for your precious reply,

sharath

[sunny_talwar]

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

[Anonymous]

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.

Waiting for reply..

Thank in advance.

Sharath

[sunny_talwar]

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?

[Anonymous]

Oh... Sorry Again... here is detailed answer for your questions;

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

[sunny_talwar]

Try this

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

[Anonymous]

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.

Waiting for your reply.

Thank you.

Sharath