topic Re: Calculate Median in QlikView

Calculate Median

nlaughton — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculate Median

sunny_talwar — Fri, 22 Nov 2019 19:33:37 GMT

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

Re: Calculate Median

nlaughton — Fri, 22 Nov 2019 19:58:49 GMT

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.

Re: Calculate Median

sunny_talwar — Fri, 22 Nov 2019 20:16:21 GMT

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

Re: Calculate Median

nlaughton — Fri, 22 Nov 2019 20:39:03 GMT

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

Re: Calculate Median

sunny_talwar — Mon, 25 Nov 2019 12:42:44 GMT

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

Re: Calculate Median

nlaughton — Mon, 25 Nov 2019 16:07:30 GMT

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.

Re: Calculate Median

sunny_talwar — Mon, 25 Nov 2019 16:31:45 GMT

May be this

Median(TOTAL Aggr(Sum(NumberRX), [End Date]))

Re: Calculate Median

nlaughton — Mon, 25 Nov 2019 17:15:08 GMT

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.

Re: Calculate Median

sunny_talwar — Mon, 25 Nov 2019 17:18:23 GMT

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

Re: Calculate Median

nlaughton — Mon, 25 Nov 2019 17:24:07 GMT

Thank you for bearing with me with my foibles and imprecise explanations, the script works great!