Graph Help: Comparing an individual to a Team without

[#1]

What am I trying to compare is an individual along side the average of everyone elses number...I can't figure it out...

I have 20 employees and I want to show how this employee selected is comparing to the other 20....

Thanks

[johnw]

Well, say you're comparing sales. Assuming your list box requires one and only one selected employee, I think this would work:

sum(sales) // selected employee's sales
avg(aggr(sum({<Employee-=Employee>}sales),Employee)) // average sales of all other employees

Assuming I got the set analysis syntax correct, that says to look at all employees except the selected employee. We group the sum(sales) by employee, then take the average.

[#3]

Is that a graph for one employee with the employee as the only dimension?

If so, you may want to look at using TOTAL inside your aggregate function. For example, if you have a chart with employee as the dimension, Sum(Sales) would give total sales for that employee. If you use Sum(TOTAL Sales), you would get total sales for all employees (the dimension is ignored). You would then probably want to divide by Count(TOTAL Employee) to get total sales per employee.

If you want average of all employees other than the employee in question, you could do (Sum(TOTAL Sales) - Sum(Sales))/(Count(TOTAL Employee) - 1). That's just a general example.

[#4]

Thanks for help...I'm using Qlikview 8.5 and I can't make the Sum(TOTAL Sales) expression work...

[#5]

I did finally get this statement to work. in a test App...

SUM

( {1} Total Revenue)

[#6]

I did finally get this statement to work. in a test App...

SUM

( {1} Total Revenue)

[#7]

Here's my statement I'm trying to use...

SUM

( {1} ALL AGGR({1}IF(SUM(If(CYTD=1,TotalNetRevenueFromStart,0)) - SUM(If(CYTD=1,PrevNetRevFromStartCalc,0)) > 0,SUM(If(CYTD=1,TotalNetRevenueFromStart,0)) - SUM(If(CYTD=1,PrevNetRevFromStartCalc,0)),0),CustomerCode))

When I have no selections the number is coming back correctly...but as soon I select a SalesPerson...my Totals are only for that one SalesPerson and not as a whole...Please HELP 🙂

[#8 · Accepted solution]

Using that {1} ignores selection. I think you need to put it in each of your Sums though. Otherwise, you are ignoring selection in the first Sum and Aggr, but not ignoring it when making the calculation. Try:

Sum({1} ALL AGGR({1}IF(SUM({1}If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1}If(CYTD=1,PrevNetRevFromStartCalc,0)) > 0,SUM({1}If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1}If(CYTD=1,PrevNetRevFromStartCalc,0)),0),CustomerCode))

I think your expression would be simplified if you used Set Analysis throughout. EDIT: For example, this piece:

SUM({1}If(CYTD=1,TotalNetRevenueFromStart,0))

Can be rewritten as:

SUM({1<CYTD = {1}>} TotalNetRevenueFromStart)

[#9]

Oh you are awesome!! It works... I have another question in terms of this... Along with this Statement is there code to figure out the top Sales Person?

So I want to show SalesPerson 1 compared to the top SalesPerson?

Thank You, Thank You , Thank You for your help...

[#10]

In theory, you should be able to get the Max of that equation grouped by customer code.

That expression is pretty complex, so it's hard to say what exactly would work. I think you could simplify it by assuming that the top sales person is going to be greater than 0. That gets rid of your outermost if statement.

Where are these expressions going to appear? If it's in a chart, what will be the dimensions?

If you have a chart with Customer as the dimension, then you may even be able to get rid of the outermost Sum and the Aggr. Then for each customer you would use:

IF(SUM({1}If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1}If(CYTD=1,PrevNetRevFromStartCalc,0)) > 0,SUM({1}If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1}If(CYTD=1,PrevNetRevFromStartCalc,0)),0)

In order to ignore dimension, you're going to need to use TOTAL in the sum. You said you had trouble getting it to work, but that is the only way I know of to ignore dimension in the expression. I would try:

IF(SUM({1} TOTAL If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1} TOTAL If(CYTD=1,PrevNetRevFromStartCalc,0)) > 0,
SUM({1} TOTAL If(CYTD=1,TotalNetRevenueFromStart,0)) -
SUM({1} TOTAL If(CYTD=1,PrevNetRevFromStartCalc,0)),0)

It may be helpful if you could post what some of your data looks like. You can completely make it up (or use Scramble), but without seeing your data structure, it's hard to tell what is needed.