CALCULATE TOP N IN TEAMDESK
Say you want to find out who are your Top 10 Users who create Thing records. I looked for this onlines, but it's not to be found. This walk-through assumes you have a User Properties table called People.
1. Go to Things
2. Create a new relation (Selection) to People, many-to-many (third choice that looks like a spider)
3. Name it Creator, so you don't confuse it with other People selections you may have
4. Under Match Conditions, add Person Column "User" = Thing Column "Created By"
This will select the one Person record that created this Thing, and give you access to all columns on the Person record.
Next, we need to figure out how this Creator Person stacks up against the others.
First, let's figure out how many Things each Person created.
5. Go to People.
6. You will see your new "Creator" relation at the bottom.
7. Add Summary on it, call it "Count of Things Created", # of records, All Records
Now we know how many Things a Person created.
Time to find out how each Person stacks up against others.
8. Still on People, create a new Relation to People. First table People, second table People.
9. Again, many-to-many spider
10. Name it "People with fewer Things". Leave Detail Views at None for now.
11. Add a match Condition: Person column "Count of Things Created" > Person column "Count of Things Created".
12. Go Back to People Columns. Under your new "People with fewer Things" Relation, ADD SUMMARY.
13. Call it "Count of People with More Things", # of records, All records. Seems super weird, but bear with me.
14. Finally, create a new Formula field on People: "Rank by Things created", Formula-Numeric
15. In the formula, all you need is [Count of People with More Things] + 1
If you want, add [Count of Things Created], [Count of People with More Things], and [Rank by Things created] to a new view or an existing view on People to check your work.
16. For the finale, go back to Things columns.
17. In your "Creator" relation at the bottom, ADD SUMMARY.
18. Call it "User Rank by Things Created", Function Maximum, Value Column "Rank by Things Created"
19. Now go back to your favorite table view or chart on Things, and filter it for "User Rank by Things Created" is less than 11.
Voila, you have your top 10.
Enjoy. Do you know a more elegant way to do this? Comments welcome. |  |
