Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lailarhc
Creator
Creator

Color by expression in bar chart (if selected average > total average)

Hello.
I have a bar chart that displays the average courses ([Nome Evento]) an employee ([Nome do servidor]) has taken in a given period of time.

I want to color the dots based on whether the average of the selection is greater than the total average or not.
I tried this as an expression in the Colors and Legend tab:
if((Count({$} distinct [Nome Evento]) /GetSelectedCount([Nome do servidor])) > (Count({1} distinct [Nome Evento]) / Count({1} [Nome do servidor])), ARGB(255,0,101,128), ARGB(255,135,32,93))

But it doesn't work. All the dots are always colored by a single color, as seen bellow:

lailarhc_0-1637773759510.png

How can I make it look like this:

lailarhc_1-1637773809466.png

But with the red color being only the values in which the average of selection is lower than the total average?

 

1 Solution

Accepted Solutions
abhijitnalekar
Specialist II
Specialist II

Hi @lailarhc ,

Please find below expression 

if((Count({$} distinct Curso) /GetSelectedCount([Nome do Servidor]))

>

(Count( total distinct Curso) / Count( total [Nome do Servidor])), ARGB(255,0,101,128), ARGB(255,135,32,93))

Note : At least more than 1 value to be selected for   [Nome do Servidor] field.

 

Regards,
Abhijit
keep Qliking...

View solution in original post

6 Replies
abhijitnalekar
Specialist II
Specialist II

Hi @lailarhc,

 

It's supposed to work.  Can you please share sample data??

Regards,
Abhijit
keep Qliking...
OmarBenSalem
Partner - Champion II
Partner - Champion II

try 

if((Count({$} distinct [Nome Evento]) /GetSelectedCount([Nome do servidor]))

>

(Count( total distinct [Nome Evento]) / Count( total [Nome do servidor])), ARGB(255,0,101,128), ARGB(255,135,32,93))

lailarhc
Creator
Creator
Author

Hello. I created a table with all the columns I'm using in my project, without any filters applies, and downloaded it as an excel file. Is that what you meant?

The file is attached.

 

abhijitnalekar
Specialist II
Specialist II

Hi @lailarhc ,

I hope you are looking for the same as below.

abhijitnalekar_0-1637844704653.png

 

Regards,
Abhijit
keep Qliking...
lailarhc
Creator
Creator
Author

Hello @abhijitnalekar , can you please post the expression you used to achieve this result? Thank you

abhijitnalekar
Specialist II
Specialist II

Hi @lailarhc ,

Please find below expression 

if((Count({$} distinct Curso) /GetSelectedCount([Nome do Servidor]))

>

(Count( total distinct Curso) / Count( total [Nome do Servidor])), ARGB(255,0,101,128), ARGB(255,135,32,93))

Note : At least more than 1 value to be selected for   [Nome do Servidor] field.

 

Regards,
Abhijit
keep Qliking...