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

# Median Mode Calculation for a measure expression

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.

Thank you,

• ###### Re: Median Mode Calculation for a measure expression

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

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

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

)

)