Forum Discussion

rp6464's avatar
rp6464
Qrew Member
2 months ago

Summary report cross tab sort?

Working in a Business management app of multiple companies with all their job info/history. Have created a summary report showing monthly revenue and GP %, with the columns grouped by month so the total rows are YTD totals for each customer. Is there a way to make it only display top 15 customers? It is auto set to alphabetical with sorting disabled because of the cross tab sort... Ideally i could sort them high to low by the YTD total rows, but it is not an option. Any ideas how to navigate this? Some companies have 100+ customers so it looks horrible as is.

reports
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 months ago

    This is most easily done using a Formula Query which would calculate the Ranking of the customer compared with all other customers.

    Here is an example of a formula numeric field for [Customer Ranking] where the field I'm ranking on is [Sales Past 12 Months] and the field ID of that field is 19.  Obviously, once you have that ranking, you can filter the report where [Customer Ranking] number is less than or equal to 15.

     

    var text QUERY = 
       "{19.GTE.'" & [Sales Past 12 Months] & "'}";

    Size(
    GetRecords($QUERY))