leaderboard position calculation

[Anonymous]

Hey there, I been developing a running app which can track your KM and compares it to other people in the company to make a leaderboard, 

To simplify it: 

• im trying to figure out how to give someone a rank depending on how much they ran.
• im also trying to figure out how to make a number incremement automatically within a gallery

To go in depth a bit more, 

I have a sharepoint list which looks like this 

Email	Distance	date
example@email.com	14	23-03-2021
example@email.com	7	23-03-2021
example2	15	22-03-2021

The way it works inside the app, is using the below code to grab each email and calculate the amount of  distance they ran. 

Sort(
    AddColumns(
        GroupBy(
            socialDistance,
             "Email",
              "ItemsByName"
        ),
        "distance",
        Sum(
            ItemsByName,
            Distance
        )
    ),
    distance,
    Descending
)

now, the next step here is to give someone a position based on the amount of unique emails in the list,
example, user1 has 25km and user2 has 36 km, therefore user2 should be given the position #1 and user 1 should be given the position #2.
Which then shows inside the app in a little box saying "position #/<# of all users>"

I tried figuring it out but it's just not working out for me, so i was hoping someone could give me a bit more insight.

The second problem is a easier problem i believe, although i did not find any information on it. 

Basically, i have a leaderboard inside a gallery.
Which has the number 1 for each item, i want it to automatically do 1+1 for each item, so that it becomes 1,2,3,etc 


Thanks in advance, please do let me know if I explained something poorly and i'll try my best to explain more.

Cheers,

Wes

[Anonymous]

Hey @WarrenBelz, sorry to bother you. but u have helped me successfully before,

u have any idea here?

thanks in advance.

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

This will add a column called Ranking based on the number of emails. I have free-typed this, so please check commas and brackets, but it should then sort by Distance

With(
   {
      wEmails:
      With(
         {
            wList:
            Sort(
               AddColumns(
                  GroupBy(
                     socialDistance,
                     "Email",
                     "ItemsByName"
                  ),
                  "distance",
                  Sum(
                     ItemsByName,
                     Distance
                  ),
                  Emails",
                  CountRows(ItemsByName)
               ),
               Emails
            )
         },
         ForAll(
            Sequence(CountRows(wList)),
            Patch(
               Last(
                  FirstN(
                     wList,
                     Value
                  )
               ),
               {Ranking: Value}
            )
         )
      )
   },
   Sort(
      wEmails,
      distance,
      Descending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Hey @WarrenBelz, this worked absolutely amazing. thank you so much. i had one more question i was hoping you could me with. is it possible to put the ranking value in a variable, which i can call again in a label somewhere else in the app? what im trying to achieve is to have a secondary label which says "your rank is #" 

[WarrenBelz]

Thanks @Anonymous ,

Instead of 

Sort(
   wEmails,
   distance,
   Descending
)

If you replace it with 

ClearCollect(
   colEMails,
   ShowColumns(
      wEmails,
      "Email",
      "Distance",
      "Emails",
      "Ranking"
   )
)

and put it on a button or other trigger, you should have the list with the fields you need.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[00rebe]

Hi @WarrenBelz 

I was just wondering if I can get some insight about this...

Is ItemsByName a new column that you have added in the code? 

And wEmails & wList, are they already in the SharePoint? Or are they new columns? 

I'm trying to do something similar but the whole code is showing back an error - replacing your columns with mine of course. 

Thanks in advance

[WarrenBelz]

H i @00rebe ,

This is an old post on a quite complex issue, but to answer your questions, ItemsByName (you can call it whatever you want) is the Grouped table in GroupBy and wList (again name it whatever) is the "variable" produced by the With() statement.

[00rebe]

Would you say that this code has changed or evolved since your post? 

If so, would you mind helping me on a similar topic 

I need to get the sum of columns per person - attached is the code & errors. 

Can't seem to figure it out - I've tried 4 or 5 different ways to code but to no success. 

The only way I've gotten it to work is by using the number of submissions - not the total points - and unfortunately I need it to be by total points. 

Help is greatly appreciated

[WarrenBelz]

@00rebe ,

Firstly, please post any code in Text also, however the syntax on what you have is correct

providing Points is a numeric field (which I assume it cannot be)

[00rebe]

Correct, points is a numeric column, I'm not sure where I'm going wrong - would there be another way to use sort & sum together?