Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Shahzad_Ahsan
Contributor II

Calculating Average in Qlik Sense

Hi Everyone

Please refer this table

Avg.jpg

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.

5 Replies
mdmukramali
Valued Contributor II

Re: Calculating Average in Qlik Sense

Dear Shahzad,

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.


devarasu07
Honored Contributor II

Re: Calculating Average in Qlik Sense

Hi,

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

=if([RECEIVE-7] < avg( total [RECEIVE-7]),Red(),Green())

4.JPG

Shahzad_Ahsan
Contributor II

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())

devarasu07
Honored Contributor II

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

mdmukramali
Valued Contributor II

Re: Calculating Average in Qlik Sense

Hi,

Can you try to use expression:

Count({<TRANSACTION_DATE ={">=Date(today()-7)"},TRANSACTION_TYPE={'RECEIVE'}>}TRANSACTION_ID)



for color condition:

=if(Count({<TRANSACTION_DATE ={">=Date(today()-7)"},TRANSACTION_TYPE={'RECEIVE'}>}TRANSACTION_ID)

>

Avg(Total Aggr(Count({<TRANSACTION_DATE ={">=Date(today()-7)"},TRANSACTION_TYPE={'RECEIVE'}>}TRANSACTION_ID),YOURDIMENSION)),green(),red())

Community Browser