Median Mode Calculation for a measure expression

[Anonymous]

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,

[swuehl]

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

[Anonymous]

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 · Accepted solution]

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

     )

)