Qlik Community

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 
Search instead for 
Did you mean: 
solomon_musayev
Contributor III
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. 

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

Thank you,

1 Solution

Accepted Solutions
swuehl
MVP
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

     )

)

View solution in original post

3 Replies
swuehl
MVP
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

solomon_musayev
Contributor III
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?

swuehl
MVP
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

     )

)