topic Re: Sales person vs top/bottom sales person per month in QlikView

Sales person vs top/bottom sales person per month

Tue, 05 Nov 2013 22:04:01 GMT

I feel I should know the answer to this, but I'm struggling. I want to plot a line chart, with months along the bottom and sales on the y-axis. The chart should show sales for a single sales person. This is, of course, easy.

I then want to plot two other lines: the first plots a line, by month, picking the top performing sales figure for that month.

The second line is the opposite: show the bottom sales figure for each month.

What I should end up with is a line representing my sales person, and then one line above and one below. I want this so each salesperson can see how they are comparing each month to the top and bottom salespersons.

I've tried lots of different ways, but can't quite manage it. I did try max(agg(sum(sales),salesperson) but this doesn't work because salesperson isn't a dimension of the chart.

Am I going down the wrong road with aggr? Is this one for set analysis?

Any help much appreciated.

Thanks

James

Re: Sales person vs top/bottom sales person per month

Carlos_Reyes — Tue, 05 Nov 2013 22:30:22 GMT

Is this what you want?

Re: Sales person vs top/bottom sales person per month

Wed, 06 Nov 2013 09:55:31 GMT

That is amazing and exactly what I needed.

Thanks so much!!

James

Re: Sales person vs top/bottom sales person per month

Wed, 06 Nov 2013 13:21:35 GMT

Carlos, could I ask you one question? I thought my set analysis was pretty good but I don't understand what {$<SalesPerson>} does (i.e. including the field but without the usual +={xx} or ={xx} , etc.) I've never seen just a field used on its own...

So I get the min, the aggr and the sum but not that bit.

Thanks again

James

Re: Re: Sales person vs top/bottom sales person per month

Wed, 06 Nov 2013 14:30:24 GMT

Hi James,

Indirectly it disregards your SalesPerson selection to get Min and Max from all sales person.

The other way to do it is , should give same result.

Min( Total <Month> {<SalesPerson=>}

aggr(

sum( {<SalesPerson=>} Sales)

,Month,SalesPerson)

)

See attached qvw

Re: Sales person vs top/bottom sales person per month

qliksus — Wed, 06 Nov 2013 14:42:29 GMT

Hi james,

If you want to get the output by using only set analysis , then you can replace the min expression with

"Min ({<SalesPerson={'*'}>}total <Month> Sales) ".

But if you want to use this method then you need to remove SalesPerson from dimension.

Both solution works fine. Depends upon your requirement.

Re: Sales person vs top/bottom sales person per month

Carlos_Reyes — Wed, 06 Nov 2013 15:14:40 GMT

James,

As others have said, when you place a field name in Set Analysis without defining its set of values, that tells QlikView to disregard any selection made on that field. There are two possible ways, you can use the equal sign or not:

Sum( {$<SalesPerson>} Sales )

Sum( {$<SalesPerson=>} Sales )

Regards,

Re: Sales person vs top/bottom sales person per month

Wed, 06 Nov 2013 15:37:10 GMT

Brilliant, thanks guys. I knew about {$<SalesPerson=>} but didn't realise you can omit the =. That's where my gap in the understanding was.

Really useful to know.

Thanks

James