I'm looking to rank clients by their gross sales. However, I'd still like to sort them in alphabetic order. What's the best way of achieving this.
Thank you everyone for your help.
You can have multiple fields as part of a sort, so, you would select the sales field first, then last name, and then first name. The names may not be necessary unless you have a number of sales that are identical, or if you've put them into a range (say for commission percentages). If this is a report you want to run regularly, I would recommend putting the sort into a script. That way it can be a button click away from showing the report.
I'd like to display a number rank. 1 would obviously be the highest grossing client.
Since 'ranking' changes based on found set and sort order, I might just make sure the highest grossest is the sort (desc). Then use the symbol to show the number.
Or this could be a field that is set with serial, if desired, but as the report changes (found & sort), this numbering would be done each time the report is run.
Thank you but how would write the formula. I took this example from philmodjunk and adapted it but it didn't work.
Let ( [ PrevRank = GetNthRecord ( contacts_JOBS::sum_AllocAmt_Client ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ) ;
PrevScore = GetNthRecord ( contacts_JOBS::sum_AllocAmt_Client; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ) ] ;
If ( PrevScore <> contacts_JOBS::sum_AllocAmt_Client ; Get ( RecordNumber ) ; PrevRank )
There are several ways to achieve this, e.g.:
ExecuteSQL ( "
WHERE gross IS NOT NULL
ORDER by gross DESC
" ; "" ; ""
ValueCount ( Left ( orderedList ; Position ( ¶ & orderedList & ¶ ; ¶ & id & ¶ ; 1 ; 1 ) ) )
where id is the primary key. You could also use a cartesian relationship to obtain a list of ids, use SortValues(), then get the position using the above expression
If you only need this for a report and/or only for a found set, do a sort by gross descending, use Replace Field Contents to batch-set a rank field with Get ( RecordNumber ), then sort by name
Thank you. I'm trying to stay away from ExecuteSQL commands. They tend to be processor hungry and slow down my computer. What are other ways. Thank you.
What are you sorting on for the report?
No calculation needed if the highest is first record (in Desc sort order).
If you need the ranking reversed for some reason, these two functions will help
Get(FoundCount) - Get(RecordNumber) + 1
(make it unstored)
I'm sorting by a client header alphabetically. In my solution client number 401 is the number one customer. I'd like the number one to display. Currently the number 401 is displaying. Thank you.
Sorry, I misunderstood. Your sort is Alpha, but the gross amount is not necessarily the first in sort order.
Ok, make a field (number), call it 'ranking' if desired.
1. Sort by the gross sales (desc)
2. Use Replace Field Contents to make the serial 1, increment 1.
3. Then sort by Alpha
The "ranking" will show the highest gross as 1 wherever it is shown in the Alpha sort.
This would be reset each time there is a new found set of records, so you might make a script to do those steps.
All the summary fields are greyed out. It won't let me sort.
It depends on what is your data to sort and rank, do you have all data in a table and show all records?
A way is using relation
value < value
Count(related::value) + 1
is the ranking. This get it on any sort order but need the value to be stored for relation.
I'm so sorry, there appears to be another post (?) which reveals more of what is needed here.
you have clients (& these will be the final sort)
you have a field which is summary (?) or calculation (?) summarizing sales. 'contacts_JOBS::sum_AllocAmt_Client', right?
is it a summary field?
Can you provide the appropriate tables (TOs), fields and relationship(s) as needed to get your report now? What fields are you showing on the report right now? And what is the context (what is the layout of the report based upon? what TO?). What parts are in the report layout?
Yes, I have clients which are sorted alphabetically. Their ranking is based on their total gross sales which is a summary (sum_AllocatedAmt_Client) field total of their allocated amount invoices when sorted by Client. The base table is CLIENTS (contains ID) and the other table is contacts_JOBS (contains ClientID_FK ). Also created a (sum_AllocatedAmt) which totals AllocatedAmt without sorting. Not sure which one I should use. Thank you so much for your time.
I may still be missing something. If "sum_AllocatedAmt" is a summary field type, then you should be able to sort by it:
Sorting records by subsummary values
So perhaps that is the summary field that should be used here. Can you sort by it?
In your report layout for the sub-summary part, what are you sorting on? It should be the CLIENTS field that is alpha-sorted.
In the sort dialog can you see this option: "Reorder based on summary field"
ALL Summary fields change based on the found set & the sort. The sort dialog with this important option will be the difference!
"Reorder..." is at the bottom of the dialog.
But on CLIENTS table you have sum_AllocatedAmt as calculation field (sum of related value), so you don't need reorder.
How about a script?
Something like this with 2 added Fields - (Rank1 & Rank2)
Show All Records
Sort Records [Restore;No Dialog] - (Sales)
Go to Record/Request/Page[First]
Go to Record/Request/Page [Next;Exit after last]
Paste [Select; Client::Rank1]
Go to Field [Client::Rank2]
Set Field [Client::Rank2;Name::Rank1+1
Copy [Select; [Client::Rank2]
Sort Records [Restore;No Dialog] - (Name)
Go to Record/Request/Page [First]
Retrieving data ...