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.

Answer 1

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.

Answer 2

Answer 3

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

Answer 4

There are over 300 records and I will need to pull the top 5%, 10%, 15% 20% for each year 2019,2020,2021 (may be up to 10 years.   The excel table above will show what the outcome needs to look like .

---

g_cat

Answer 5

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.

Answer 6

Sorry I have not responded;  I was on vacation.   I am not sure what you mean by Select * from matrix_test

Am I supposed to create a new DataSet and then use select * from matrix_test and then create a 2nd DataSet with the modifications?

---

g_cat

Answer 7

I think part of the Original DataSet where you are showing a select statement is cut off.  

---

g_cat

Answer 8

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.

Answer 9

Yes, your example is what I am looking to do with my data.  I will try to use your info to build a new DataSet. 

---

g_cat

Answer 10

I tried to use your example, however I keep getting an error  Invalid column name 'date1'   .

I used PaymentYear in my report and I tried using that in place of date1 and I still get an error Invalid column name 'PaymentYear'

---

g_cat

Answer 11

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.

Answer 12

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

Answer 13

 I still need help adding the top 5 into a separate table as shown above.

How can i add the Principal for the column Rank if it is between 1 through 5.     

---

g_cat

Answer 14

---

g_cat

Answer 15

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.