Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating median on an aggregate value

I have a data set that includes sales by sales rep. I need to find out what the median sale total is by rep. Whenever I use the median equation it gives me the median sale value, not the median sale value by rep. Can someone help me with this. I am sure it is something simple that I am missing.

Thanks

Naomi

1 Solution

Accepted Solutions
johnw
MVP
MVP

The set needs to be applied both places:

median({your set} aggr(sum({your set} Sales),SalesRep))

View solution in original post

5 Replies
johnw
MVP
MVP

I believe this:

median(aggr(sum(Sales),SalesRep))

Assuming I got that right, it says to sum the sales by sales rep, and then take the median of those sums.

Not applicable

This works, but when I try to put set analysis in, it stops working. This is my equation

=median({1 <[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>} aggr( sum([Calculated Sold Value SRP]),[Partner Rep]))

I am trying to make sure that it always gives us the median for this fiscal year, for only US & CA, for those that are Closed regardless of the parameters that are selected. I think that the {1 < should do that, but it isn't. If I select these parameters from my lists, then I get the right number, but I want it to calculate without making the selection. Do you see something that I entered wrong?

johnw
MVP
MVP

The set needs to be applied both places:

median({your set} aggr(sum({your set} Sales),SalesRep))

View solution in original post

Not applicable

That worked!!! Thank you so much!

tahernia_p
Contributor II
Contributor II

Thanks for your great formula, it works for my problem too