Answered by:
Sort by top 5% for Top 5 largest values

Question
-
I am trying to pull the Top 5 largest values from the Principal. I need to do it for each year that is displaying. Some may have up to 20 or 30 years. I have tried the filter in the Groups, but this doesn't seem to work. I need all the top 5 for 2019 , top 5 for 2020 etc.
Thank you.
- Edited by g_cat Wednesday, July 17, 2019 10:26 PM
Answers
-
Hi
It is better for you to create another dataset with the top 5 value and the set it in the new table .
BR,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
- Marked as answer by g_cat Thursday, August 15, 2019 3:33 PM
All replies
-
Hi g_cat
What the detailed excepted matrix you want , according to your image and the description , seems you want to get the top 5 of Principal.
If so , assume you have year 2019 to 2028 , 10 year total , then you would get 50 value .(5*10)
Then this 50 value would distributed in different client . then the matrix would like below:
2019
2020
2021
…
Client1
3%
7%
Client2
4%
Client3
5%
Client4
6%
…
There would exist a lot of blank in your matrix .
Generally , the top 5 value is not suitable to render in the matrix .
If you want to render the value in matrix like below , seems you could try to get it by change your query .(if so and if you need ,I would offer it in my next reply)
So , what the detailed excepted matrix render? You could try to draw it in excel and then offer the image to us .
Hoping for your reply.
Best Regards,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
- Edited by Mitarai Queen Thursday, July 18, 2019 3:18 AM
-
-
Hi g_cat
sorry for make you misunderstood.
I just want to know the expected table preview you want .
Do you want to highlight the top5 value in your table or others ?
Seems it is not related to the query .
Hoping for your reply.
Best Regards,
Eric LiuBest Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
-
-
Hi g_cat
Based on your description ,seems you could try to check the following example and then test if it works fine .
Original dataset :
Then the modified dataset :
martix preview:
Preview:
Seems it was not available to add the filter in matrix , you could try to use the visibility to achieve it :
Hope it can help you.
Best Regards,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
- Edited by Mitarai Queen Monday, July 22, 2019 3:02 AM
- Proposed as answer by Mitarai Queen Thursday, July 25, 2019 9:12 AM
-
-
-
Hi
If you want , you could try to build the example dataset which I mentioned in my pic .
What I mean is ,does the result are you wanted or not ?
There are two query , one show the original data in table , one show the modified data from table .
You could see the modified query from the second image which the query was cut off in the first image .
So , does the result are your needed ?
Hoping for your reply.
Best Regards,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
-
-
-
Hi
use the following query:
;with martix_test as ( select 'A' as client_name , 1000 as sales ,'2019-01-01' as date1 union all select 'A' , 1001 ,'2020-01-01' union all select 'A' , 1002 ,'2021-01-01' union all select 'B' , 1003 ,'2019-01-01' union all select 'B' , 1004 ,'2020-01-01' union all select 'B' , 1005 ,'2021-01-01' union all select 'V' , 1006 ,'2019-01-01' union all select 'V' , 1007 ,'2020-01-01' union all select 'V' , 1008 ,'2021-01-01' union all select 'C' , 1009 ,'2019-01-01' union all select 'C' , 1010 ,'2020-01-01' union all select 'C' , 1011 ,'2021-01-01' union all select 'A' , 1029 ,'2019-01-01' union all select 'B' , 1110 ,'2020-01-01' union all select 'V' , 2011 ,'2021-01-01' ) select client_name,year(date1) as year1,sum(sales/2000.0) as rate,rank() over (partition by year(date1) order by sum(sales/2000.0) desc) as rank1 from martix_test group by client_name,year(date1) order by rate desc
Hope it can help you.
Best Regards,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
-
I figured out how to sort the data by Principal and separate it by year. I have a Running Value for the "Rank" how can i add the Principal for the column Rank if it is between 1 through 5.
g_cat
- Proposed as answer by Mitarai Queen Tuesday, August 13, 2019 1:18 AM
-
-
-
Hi
It is better for you to create another dataset with the top 5 value and the set it in the new table .
BR,
Eric Liu
Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.
- Marked as answer by g_cat Thursday, August 15, 2019 3:33 PM