# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Register for Events and Webinars directly from Qlik Community: http://bit.ly/2Vpnenx
New Contributor III

## Sum of distinct count of an expression traffic light style graph

I am trying to use a traffic light system in  chart

So far I have created the graph with the distinct count of the items, i have also created an average count which also works.

But I am looking for help to create a total of the average count of items so that I can change the bar colour to green if it is below the total average, red if it is above total average, otherwise colour it orange.

I have tried many different combinations of avg / aggr/ total but I cannot seem to find the solution.

Has anyone solved this?

Labels (7)

• ### using variables

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Sum of distinct count of an expression traffic light style graph

Hi, it will better if you can upload a sample to play with.

It should be something like: Avg(TOTAL Aggr(Expression, Dimension))

Or add a variable that calculates the average with just (it's important to keep the initial '=' in variable value): =Avg(Aggr(Expression, dimensions)

And use in expression like: If(Expression>\$(AvgVariableName), ...)

2 Replies
Highlighted
MVP

## Re: Sum of distinct count of an expression traffic light style graph

Hi, it will better if you can upload a sample to play with.

It should be something like: Avg(TOTAL Aggr(Expression, Dimension))

Or add a variable that calculates the average with just (it's important to keep the initial '=' in variable value): =Avg(Aggr(Expression, dimensions)

And use in expression like: If(Expression>\$(AvgVariableName), ...)

Highlighted
New Contributor III

## Re: Sum of distinct count of an expression traffic light style graph

Hi Ruben,

The solution of Avg(TOTAL Aggr(Expression, Dimension)) solved my issue.

Many thanks,

Shona