# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## 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,

1 Solution

Accepted Solutions
MVP

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

)

)

3 Replies
MVP

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

Contributor III
Author

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?

MVP

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

)

)