none
Sort by top 5% for Top 5 largest values RRS feed

  • 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
    Wednesday, July 17, 2019 10:23 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
    Wednesday, August 14, 2019 3:18 AM

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 Eric Liu001 Thursday, July 18, 2019 3:18 AM
    Thursday, July 18, 2019 3:12 AM

    • Edited by g_cat Friday, July 19, 2019 2:03 PM
    Thursday, July 18, 2019 2:15 PM
  • 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.

    Friday, July 19, 2019 5:51 AM
  • 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

    Friday, July 19, 2019 2:02 PM
  • 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 Eric Liu001 Monday, July 22, 2019 3:02 AM
    • Proposed as answer by Eric Liu001 Thursday, July 25, 2019 9:12 AM
    Monday, July 22, 2019 3:01 AM
  • 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

    Tuesday, August 6, 2019 10:03 PM
  • I think part of the Original DataSet where you are showing a select statement is cut off.  

    g_cat

    Wednesday, August 7, 2019 1:46 PM
  • 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.

    Thursday, August 8, 2019 2:16 AM
  • 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

    Thursday, August 8, 2019 2:36 PM
  • 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

    Thursday, August 8, 2019 7:23 PM
  • 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.

    Friday, August 9, 2019 2:48 AM
  • 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 Eric Liu001 Tuesday, August 13, 2019 1:18 AM
    Monday, August 12, 2019 9:07 PM
  •  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

    Tuesday, August 13, 2019 1:58 PM

  • g_cat

    Tuesday, August 13, 2019 8:22 PM
  • 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
    Wednesday, August 14, 2019 3:18 AM