topic Calculating median on an aggregate value in QlikView

Calculating median on an aggregate value

Thu, 24 Feb 2011 23:30:54 GMT

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

Calculating median on an aggregate value

johnw — Fri, 25 Feb 2011 00:00:36 GMT

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.

Calculating median on an aggregate value

Fri, 25 Feb 2011 00:20:29 GMT

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?

Calculating median on an aggregate value

johnw — Fri, 25 Feb 2011 00:28:57 GMT

The set needs to be applied both places:

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

Calculating median on an aggregate value

Fri, 25 Feb 2011 00:32:01 GMT

That worked!!! Thank you so much!

Re: Calculating median on an aggregate value

tahernia_p — Sun, 12 Jan 2020 13:24:26 GMT

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