Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

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
Specialist III
Specialist III

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
Master II
Master II

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
Creator III
Creator III
Author

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
Master II
Master II

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
Specialist III
Specialist III

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