3 Replies Latest reply: Feb 8, 2017 3:12 PM by Stefan Wühl RSS

    Median Mode Calculation for a measure expression

    Salamon Musayev

      Hello,

       

       

      I am having a hard rime calculating and validating the mode and median in Qlik Sense.

       

      I have a field called PhycisianCompliance with yes or no options.

       

      From that field I calculate a compliance measure by using

      Count({<PhycisianCompliance = {'Yes'}>}PhycisianCompliance)   /   Count(PhycisianCompliance)

       

      This gives me compliance rate of 88%.

       

      I then try to trend this over a 12 month period and see the lows and highs of compliance rate.  I would like to include in the trend the a reference line for Median and Mode.

       

      I cannot for example do the following in expression Mode(Count({<PhycisianCompliance = {'Yes'}>}PhycisianCompliance)   /   Count(PhycisianCompliance))  give me  a error. 

       

      I was however able to create a variable

      vPhyVteCompliant= '=Count({<PhycisianCompliance = {'Yes'}>}PhycisianCompliance)   /   Count(PhycisianCompliance)'

       

      Then I apply Median($(vPhyVteCompliant)) or Mode(v$(vPhyVteCompliant))  however both rates give me the same number as the compliance rate.  Hence I am worried that its not giving me the real Median or Mode. 

       

      Can someone please help with where I am going wrong or give me another suggestions.

       

       

      Thank you,

        • Re: Median Mode Calculation for a measure expression
          Stefan Wühl

          If you want to embed an aggregation function like Count() into another aggregation function like Median(), then in most cases you need to use advanced aggregation, i.e. the Aggr() function:

           

          Something like

           

          =Median( TOTAL

               Aggr(

                    Count({<PhycisianCompliance = {'Yes'}>}PhycisianCompliance)   /   Count(PhycisianCompliance)

                     ,YourField(s)ToGroupByForExampleThePeriodFieldInYourChart

               )

          )

           

          Pitfalls of the Aggr function

            • Re: Median Mode Calculation for a measure expression
              Salamon Musayev

              Hi Stefan,

               

              Thank you for the suggestion,  I am still having an issue with Median and Mode function and I am hoping this example may clarify.

               

              I have data were I calculate the number of test each patient underwent during the hospital stay. 

              I am able to create a table with a dimension Patient#  as such

              if(PatientTest = 'Test1' or Patient Test = 'test2', PatientNumbea)

              and

              Measure  =   Count({<TestList = {'Test1', 'Test2'} >} PatientNumber)

               

              My Table looks the following

              PatientNumber              NumberOfTest

              Patient#1                        6

              Patient#2                        5

              Patient#3                        5

              Patient#4                        2

              Patient#5                        1


              Any advice how I can get the Median and Mode for this?

                • Re: Median Mode Calculation for a measure expression
                  Stefan Wühl

                  It would make it much easier to help if you could provide some sample data, best included in a sample QVW.

                   

                  You can't use calculated dimensions in aggr() function, but maybe you can achieve what you want by filtering the record set accordingly:

                   

                  =Median({<[Patient Test] = {'Test1', 'test2'}>}

                       Aggr(

                            Count({<TestList = {'Test1', 'Test2'} >} PatientNumber)

                            , PatientNumbea

                       )

                  )