Vary Chart Colour By Value

AppSheet AppSheet Q&A
,
1

Hey guys,

So, I know we’ve covered this, but I’m picking up on the thread started by @Koichi_Tsuji on Radial Gauges using QuickChart.

CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(0, 100, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: true,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
)

I have this inspired expression that handles the generation of charts in my app perfectly. What I’d like to do is have the RGB change based on the [Percentage] value. Is this possible in AppScript?
Searching around, it appears that people have achieved the same in java here, but I honestly haven’t a clue how to translate it.

Would be grateful for any help.

2

That’s not a “script”. It’s just a URL to the quickchart.io service, with a JSON object of parameters.

https://quickchart.io/

https://community.quickchart.io/

https://quickchart.io/documentation/

You just need to replace the rgb values section(s) with a conditional expression, like IFS() or SWITCH(), that outputs different color values for different percentage values.

https://help.appsheet.com/en/articles/2355961-ifs

https://help.appsheet.com/en/articles/2355972-switch

3 Likes
3

I understand IF statements and how to use them, where I’m struggling is how to insert it into the expression.

Positioning it in-line just throws an error.

backgroundColor:[“,IF([Percentage] < 50, ‘rgb(0, 100, 0)’, ‘rgb(100, 0, 0)’),”]

4

What error?

5

Great question.

Chart error: ReferenceError: rgb is not defined

Unfortunately, this is already beyond my ken.

6

Where does that error occur? Please post a screenshot of the entire screen.

7

Here’s the full expression. I’m guessing I’m misusing the rgb option.

CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:[",IF([Percentage] < 50, 'rgb(0, 100, 0)', 'rgb(100, 0, 0)'),"]
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
)

I have set the column as an IMAGE and use it as shown here:

Qiro_0-1651179409387.png
Qiro_0-1651179409387.png1475×1032 126 KB

If I replace the entire rgb IF statement with a colour, it displays correctly.

Qiro_1-1651179543986.png
Qiro_1-1651179543986.png382×658 32.1 KB

8

Found a solution that works. It’s not pretty, but it functions as expected:

The IF statement should not be in-line with the rest of the expression, instead, the CONCATENATED expression needs to be wrapped in an IF statement.

IF([Total Score] > 50,
CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(0, 100, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
),
CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(100, 0, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
))

There must be a better way, but I don’t know what it is.

This expression just checks to see what the [Percentage] is and displays a static image based on the result.