
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How are you getting 80913 for 4/25/2019? NumberRX values are so small... how did they add up such a big number?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Based on the data that you have shared, would you be able to share the output you expect to see?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Once again... how are you getting to these numbers? Would you be able to share a sample where I can see this?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Median(TOTAL Aggr(Sum(NumberRX), [End Date]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- « Previous Replies
-
- 1
- 2
- Next Replies »