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

    Calculating Average in Qlik Sense

    Shahzad Ahsan

      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.

        • Re: Calculating Average in Qlik Sense
          Mohammed Mukram Ali

          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.


          • Re: Calculating Average in Qlik Sense
            Devarasu R

            Hi,

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

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

             

            4.JPG

            • Re: Calculating Average in Qlik Sense
              Shahzad Ahsan

              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
                  Devarasu R

                  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
                    Mohammed Mukram Ali

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