Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nlaughton
Contributor II
Contributor II

Calculate Median

Hello, 

 

i've tried to read other threads/questions  and cannot quite get it correct. I'm pretty new to Qlik,forgive me if my terms are imprecise. my  I'm trying to calculate a median for an expression that contains a sum. 

a sample of my underlying data looks like this. 

End DatePrintNameUnitCostNumberRXQTY
4/25/2019ACETAMINOPHEN 325MG TAB0.00535500
4/25/2019ALLOPURINOL 300MG TAB0.0628260
4/25/2019ALLOPURINOL 300MG TAB0.0787130
4/25/2019ALLOPURINOL 100MG TAB0.042390
4/25/2019AMITRIPTYLINE HCL 50MG TAB0.1799130
5/27/2019BUPROPION HCL 150MG 12HR SA TAB0.0695390
5/27/2019BENZOYL PEROXIDE 5% (WATER BASED) GEL0.1155160
5/27/2019BUPROPION HCL 150MG 24HR SA TAB0.216390
5/27/2019BUPROPION HCL 300MG 24HR SA TAB0.2247260
5/27/2019CALCITRIOL 0.25MCG CAP0.3164115
5/27/2019CAPSAICIN 0.025% CREAM0.035160
5/27/2019CLINDAMYCIN PHOSPHATE 1% TOP GEL1.1303130
5/27/2019CLONIDINE HCL 0.1MG TAB0.0139160
6/23/2019FLUOXETINE HCL 20MG CAP0.01827330
6/23/2019FOLIC ACID 1MG TAB0.05585150
6/23/2019FUROSEMIDE 20MG TAB0.004160
6/23/2019FUROSEMIDE 40MG TAB0.02693120
6/23/2019FUROSEMIDE 80MG TAB0.074260
6/23/2019FLUOXETINE HCL 10MG CAP0.0202130
6/23/2019FLUTICASONE PROP 50MCG 120D NASAL INHL6.1211
6/23/2019FLUTICASONE PROP 50MCG 120D NASAL INHL2.961010
6/23/2019FENOFIBRATE 54MG TAB0.2009115

 

i have the data aggregated by ENDDATE,  again here is a sample 

End Datesum(NumberRX)Median
4/25/201980913?
5/27/201981040?
6/23/201978227?
7/27/201991562?

What i am looking for a is an expression to find the median of the "sum(NumberRX)" 

 

Thanks for  everyone's help.

 
 

 

1 Solution

Accepted Solutions
sunny_talwar

I think the expressions works with few changes

1) Field name in the sample is ENDDATE, but you use enddate. Qlik is case sensitive.

2) Missing parenthesis at the end

image.png

=Median(TOTAL Aggr(Sum(NumberRX), [ENDDATE]))

 

View solution in original post

10 Replies
sunny_talwar

How are you getting 80913 for 4/25/2019? NumberRX values are so small... how did they add up such a big number?

nlaughton
Contributor II
Contributor II
Author

sorry if my initial post was confusing....What i posted was just the first few lines of my data , the actual data does add up to those large numbers in the sum column. 

sunny_talwar

Based on the data that you have shared, would you be able to share the output you expect to see?

nlaughton
Contributor II
Contributor II
Author

Based on the given Sums, i would expect the Median to be 8976.5 but  i cannot figure out how to calculate that 

 

End Datesum(NumberRX)Median
4/25/201980913

8976.5

5/27/2019810408976.5
6/23/2019782278976.5
7/27/2019915628976.5
sunny_talwar

Once again... how are you getting to these numbers? Would you be able to share a sample where I can see this?

nlaughton
Contributor II
Contributor II
Author

sorry i keep giving obscure examples and not describing what i'm trying to achieve more clearly. 

 

With the above examples, i'm trying to find the Median of the sum(NumberRx) to manually calculate this, i would arrange the values from smallest to largest...78227, 80913, 81040, 91562. and then to find the Median value i would take the middle value, because there are 4 values in this example, i would add the two middle numbers and divide by 2 (80913 +81040 )/2 =80976.5. 

sunny_talwar

May be this

Median(TOTAL Aggr(Sum(NumberRX), [End Date]))
nlaughton
Contributor II
Contributor II
Author

i made a sample file with some sample data maybe this will be easier. i uploaded some sample excel data and a basic qlikview file that shows what i'm trying to figure out. 

 

sunny_talwar

I think the expressions works with few changes

1) Field name in the sample is ENDDATE, but you use enddate. Qlik is case sensitive.

2) Missing parenthesis at the end

image.png

=Median(TOTAL Aggr(Sum(NumberRX), [ENDDATE]))