topic Graph Help: Comparing an individual to a Team without in QlikView

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 03:48:25 GMT

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

Graph Help: Comparing an individual to a Team without

johnw — Thu, 05 Nov 2009 04:18:08 GMT

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.

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 04:20:17 GMT

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.

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 09:45:54 GMT

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

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 11:27:32 GMT

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

SUM

( {1} Total Revenue)

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 11:27:41 GMT

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

SUM

( {1} Total Revenue)

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 22:17:11 GMT

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 🙂

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 23:28:39 GMT

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)

Graph Help: Comparing an individual to a Team without

Thu, 05 Nov 2009 23:41:33 GMT

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...

Graph Help: Comparing an individual to a Team without

Fri, 06 Nov 2009 02:42:46 GMT

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.

Graph Help: Comparing an individual to a Team without

Fri, 06 Nov 2009 04:51:36 GMT

I've attached the Qlikview App along with Set Statement that wasn't working along with the solution you gave me which is working :).

Thanks again...I'm looking for a similiar number like the Set Example which would give me the Top Sales Person even if I don't have them selected.

I hope the upload worked.

Thanks

Graph Help: Comparing an individual to a Team without

johnw — Fri, 06 Nov 2009 05:10:02 GMT

OK, see attached.

Dimension = if(aggr(rank(SUM(AGGR(IF(SUM([YTD Net Rev]) - SUM([PYTD Net Rev]) > 0,SUM([YTD Net Rev]) - SUM([PYTD Net Rev]),0),CustomerName))),Corpsales)=1,dual(Corpsales,1),dual('ALL OTHERS',2))

Expression = SUM(AGGR(IF(SUM([YTD Net Rev]) - SUM([PYTD Net Rev]) > 0,SUM([YTD Net Rev]) - SUM([PYTD Net Rev]),0),CustomerName))

Graph Help: Comparing an individual to a Team without

Fri, 06 Nov 2009 05:28:04 GMT

Hi John, thanks for the repsonse...

It's very close for what I'm looking for...

What I'm trying to do is...if I Select Scott on the Corpsales that in the report I still hold what the amount is for the top Sales Person. Similiar to the Set Command but can't figure out how to make the rank work with it for one person.

The help is very appreciated...

Graph Help: Comparing an individual to a Team without

johnw — Fri, 06 Nov 2009 05:52:47 GMT

Ah, OK. Apply a liberal sprinkling of {1} then? Or a liberal sprinkling of {<Corpsales=>} if you want all other selections to still apply. Attached uses {1}.

Graph Help: Comparing an individual to a Team without

Fri, 06 Nov 2009 06:17:56 GMT

I'm not getting how to move the rank feature has an expression...

I would to show always the Top Sales Person in the Columns as data...

Thanks again for the help...