Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
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
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
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?

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!

Contributor II
Contributor II

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