Calculate Median

[nlaughton]

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 Date	PrintName	UnitCost	NumberRX	QTY
4/25/2019	ACETAMINOPHEN 325MG TAB	0.0053	5	500
4/25/2019	ALLOPURINOL 300MG TAB	0.0628	2	60
4/25/2019	ALLOPURINOL 300MG TAB	0.0787	1	30
4/25/2019	ALLOPURINOL 100MG TAB	0.042	3	90
4/25/2019	AMITRIPTYLINE HCL 50MG TAB	0.1799	1	30
5/27/2019	BUPROPION HCL 150MG 12HR SA TAB	0.0695	3	90
5/27/2019	BENZOYL PEROXIDE 5% (WATER BASED) GEL	0.1155	1	60
5/27/2019	BUPROPION HCL 150MG 24HR SA TAB	0.216	3	90
5/27/2019	BUPROPION HCL 300MG 24HR SA TAB	0.2247	2	60
5/27/2019	CALCITRIOL 0.25MCG CAP	0.3164	1	15
5/27/2019	CAPSAICIN 0.025% CREAM	0.035	1	60
5/27/2019	CLINDAMYCIN PHOSPHATE 1% TOP GEL	1.1303	1	30
5/27/2019	CLONIDINE HCL 0.1MG TAB	0.0139	1	60
6/23/2019	FLUOXETINE HCL 20MG CAP	0.0182	7	330
6/23/2019	FOLIC ACID 1MG TAB	0.0558	5	150
6/23/2019	FUROSEMIDE 20MG TAB	0.004	1	60
6/23/2019	FUROSEMIDE 40MG TAB	0.0269	3	120
6/23/2019	FUROSEMIDE 80MG TAB	0.074	2	60
6/23/2019	FLUOXETINE HCL 10MG CAP	0.0202	1	30
6/23/2019	FLUTICASONE PROP 50MCG 120D NASAL INHL	6.12	1	1
6/23/2019	FLUTICASONE PROP 50MCG 120D NASAL INHL	2.96	10	10
6/23/2019	FENOFIBRATE 54MG TAB	0.2009	1	15

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

End Date	sum(NumberRX)	Median
4/25/2019	80913	?
5/27/2019	81040	?
6/23/2019	78227	?
7/27/2019	91562	?

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

Thanks for  everyone's help.

[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]

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]

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

End Date	sum(NumberRX)	Median
4/25/2019	80913	8976.5
5/27/2019	81040	8976.5
6/23/2019	78227	8976.5
7/27/2019	91562	8976.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]

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]

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

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

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