Grouping numbers into Ranges with a Corresponding Label

[nathanlo9]

Hi guys, noob here. I’m trying to create and display a label which summarizes a numeric value for each item in a table. The numeric column is a rating on a scale of 1-100 and the label will represent a range of values such as 95 to 100, 80 to 94, 70 to 79, etc.

In Excel I would build groups in a pivot table or use a VLOOKUP on the source table, but I can’t figure out how to do the equivalent in AppSheet.

My app is a little personal project that let’s me review video games. I have only a single table (GAMES) with a column called “SCORE”. I added a virtual column called “Score Range” to hold the new label and used a Switch expression thus:

SWITCH(
  [SCORE] >= 95, "Classic",
  [SCORE] >= 86, "Amazing",
  [SCORE] >= 80, "Very Good",
  [SCORE] >= 70, "Good",
  [SCORE] >= 60, "Your mileage may vary",
  [SCORE] >= 50, "Not Bad, but Flawed",
  [SCORE] >= 40, "Bad",
  [SCORE] >= 25, "Horrible",
  [SCORE] < 25, "You've been warned",
  ISBLANK([SCORE]), "none"
)

However, the output of the Switch is a Yes/No value rather than the Text label that I want. I tried IFS and got the same result. Any idea what I can use to get the text I’m after?

Thanks in advance

[Steve]

This should work fine:

IFS(
  [SCORE] >= 95, "Classic",
  [SCORE] >= 86, "Amazing",
  [SCORE] >= 80, "Very Good",
  [SCORE] >= 70, "Good",
  [SCORE] >= 60, "Your mileage may vary",
  [SCORE] >= 50, "Not Bad, but Flawed",
  [SCORE] >= 40, "Bad",
  [SCORE] >= 25, "Horrible",
  [SCORE] < 25, "You've been warned",
  ISBLANK([SCORE]), "none"
)

[nathanlo9]

Thank you for the response. I tried exactly that but I still get a Yes/No return rather than Text.
In case it matters, the SCORE column is calculated using an App Formula

[Steve]

Please post a screenshot.

[nathanlo9]

[nathanlo9]

[nathanlo9]

Here’s a cropped selection to show the Test results of the expression along with the actual (on the far right).

[Steve]

What does it look like in the app itself?

[nathanlo9]

It shows as a “N” value for each row that isn’t in the first category (SCORE >= 95). For those rows the value shows as “Y”

[Steve]

Please post a screenshot of the entire configuration for the Score Range column, and a screenshot of the column list (from Data >> Columns in the app editor) for the SNES table.

[nathanlo9]

Certainly! Here’s the column configuration. There’s nothing fancy other than the formula

[nathanlo9]

And here are all the columns

[nathanlo9 · Accepted solution]

Ah I found the problem. It was because I was setting the initial value of the [Gameplay score] column using a Predict model. That column is used in the formula of the SCORE column. I removed that Prediction and the Score Range works!

[Steve]

Good catch!

[nathanlo9]

Thank you very much for your time