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 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
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
Hi Ali,
I tried that way, but its returns top 10 record based on field2 for entire duration not for selected period.
sharath
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
Can you share screenshot which uu reply to sunny? Even esc function key can also work
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
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
varFrom and varTo are static values? they are not an expression?
No Sunny, Based on date selection respective values come, its dynamic.
Sharath