Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
)
)
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
)
)
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?
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
)
)