Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
nlaughton
New 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
Highlighted

Re: Calculate Median

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
Highlighted

Re: Calculate Median

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

Highlighted
nlaughton
New Contributor II

Re: Calculate Median

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. 

Highlighted

Re: Calculate Median

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

Highlighted
nlaughton
New Contributor II

Re: Calculate Median

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
Highlighted

Re: Calculate Median

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

Highlighted
nlaughton
New Contributor II

Re: Calculate Median

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. 

Highlighted

Re: Calculate Median

May be this

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

Re: Calculate Median

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. 

 

Highlighted

Re: Calculate Median

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