5 Replies Latest reply: Dec 14, 2017 4:31 AM by Mohammed Mukram Ali

# Calculating Average in Qlik Sense

Hi Everyone

The top value (8) is showing Average for this column.

What I need is, the values which are below average should show in Red Color Else Green

here the values which are below 8, show in Red else Green.

This should work for filter also, suppose If I select only 10 records , result should show for only those 10 selected records.

• ###### Re: Calculating Average in Qlik Sense

You can use Avg with Aggregation to achieve it.

for example if you have

Dimension : CategoryName

Expression: Sum(Sales)

=if(Sum(Sales)>Avg(total Aggr(sum({<Sales={">0"}>}Sales),CategoryName)),green(),Red())

Kindly find the attached example for better understanding.

Thanks,

Mukram.

• ###### Re: Calculating Average in Qlik Sense

Hi,

try like this at your chart object -->dimension text color expression

• ###### Re: Calculating Average in Qlik Sense

Hi Mohammed & Devarasu

Both of your answer is working as a demo but not for my live data

My Measure is:

Count(if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID))

and I am using this condition for color but its not working:

if((Count(if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID))) >

Avg(total (if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID))),Green(),Red())

• ###### Re: Calculating Average in Qlik Sense

Hi,

Try like this?

=if(

Count(if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID)) <

avg( total Count(if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID)))

,Red(),Green() )

or try this

=if( Count(if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID)) <

(Count(total if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID)) / Count(distinct if(TRANSACTION_DATE >= today()-7 and TRANSACTION_TYPE='RECEIVE', TRANSACTION_ID)))

,Red(),Green())

If still not working, try to share your mock app with sample data.

Thanks,

Deva

• ###### Re: Calculating Average in Qlik Sense

Hi,

Can you try to use expression: