topic Re: Variable value not taken by chart in App Development

Variable value not taken by chart

DEMONIO_AZUL — Wed, 11 Dec 2024 18:24:33 GMT

Hello there!

I have a bar chart that should look like this:

The average value is stored in a variable defined like this:

AverageSales = Avg({1<[Full Name]={*}>} Sales)

Note: [Full Name] is the name of the dimension I use for each Salesperson.

I use it to plot the Add-on Reference line, which has the correct value, 460.77.

To colour the bars, all bars that are above the average must be in green, all the rest are red.

It works with the numeric value in the Appearance section where I input the value, but it does not work otherwise. The variable does not work, neither the very same expression that defines the variable... but it does work on the Add-on Reference line.

What am I doing wrong?

I have tried all these, but only the numeric value works:

Any help will be appreciated.

Thank you!

Re: Variable value not taken by chart

NoahF — Wed, 11 Dec 2024 19:15:23 GMT

Hi @DEMONIO_AZUL ,

since your are ignoring selections in your Set Analysis anyways. You could also just calculate the average in the script beforehand and store it into a variable.

Avg_Table: LOAD Avg(Sales) AS AverageSales RESIDENT Sales_Table; // Store the average in a variable LET vAverageSales = Peek('AverageSales'); Drop table Avg_Table;

Then your can just use your average $(vAverageSales) in the color expression.

Regards

Noah

Re: Variable value not taken by chart

MatheusC — Wed, 11 Dec 2024 19:20:15 GMT

Hi, @DEMONIO_AZUL

Try using the total qualifier in your avg() expression

AverageSales = Avg(total expression)

if(sum(Sales)>=$(AverageSales),color1,color2)

- Regards, Matheus

Re: Variable value not taken by chart

DEMONIO_AZUL — Tue, 17 Dec 2024 14:48:29 GMT

Thank you @MatheusC . This was useful. The expression as you posted it was not working either, but this, with your idea, did the trick: