I have a list of items. The app allows users to rate the items by inputting a user specific score (1 - 10). The values are stored in a data column that is specified to be a number, it is restricted to values 1 - 10 and is user specific. So far so good. For example, an item is an apple and receives 3 user ratings : 8,9 & 10.
How can I have add a new data column that calculates the average rating for the apple, e.g. 9 out of 10? Will it work if I simply add a maths column and use Average() ? Will it see the rating entry has many user values or will it only âsee the single value enteredâ by the current user?!
First up, if you want an average, you must catch those ratings in separate rows, in another sheet.
Then you can make a relation that match the item name in the current sheet with the item name in the newly created sheet, set to match multiple, then rollup the average of that relationâs ratings.
We donât have rollup over user-specific columns now.
I have another idea that wouldnât require additional rows in another sheet, but first I have a question. Once a rating is submitted, can the user ever change it again?
To answer your last question, no you canât use a math column to average a user specific column value from all users. The calculation happens on the userâs device and the only value on the device is the value thatâs specific to the user. There is no access to the user specific value of other users.
If a user wonât be allowed to change the rating, then what Iâm thinking is that you can use something like a choice component so a user can choose a rating and it will be saved to the user specific column you already created. Next you would create two non-user specific columns. One to hold a âtotal ratingâ amount, and one to hold a âtotal countâ. Next youâll probably need another user specific numeric column to hold the âsaved ratingâ value to indicate if a user âratedâ the item or not. Now you can create a button or some other component that will allow you to create a custom action. In the custom action, you can use the increment action to increment the âtotal ratingâ column by amount the user chose, create another increment action to increment the âtotal countâ by 1, and finally, the set column action to save the rating value to the âsaved ratingâ, so we know if they have already rated the item or not.
Now that we have the columns filled, you can create a math column to calculate the average from the âtotal ratingâ and âtotal countâ columns and display the average in the item details. Also, we can set visibility to hide the choice component and button once the âsaved ratingâ column is greater than 0 so the user cannot update the rating again.
If you ever wanted to allow the user to change their rating, then there are a few extra steps. First we will need a math column that takes the âsaved ratingâ value and multiplies by -1. Instead of hiding the choice and button components, they would always be visible. What we then have to do is modify the custom action to first perform an IF to check it the âsaved ratingâ is 0. If it is then it can perform the rest of the actions that we set before. Else, we will need to first increment the âtotal ratingâ, by the negative amount calculated in the math column. Next we will need to increment the âtotal countâ by -1. Finally, now that we have reversed the previous rating, we will need to set up the rest of the actions like we did before to add the new rating through increments and set set columns.
So, something similar to this, I have custom ratings for my app (user-specific) and want to build a pseudo recommendation system or at least show users similar elements that other rated similarly. Thoughts?