Sales person vs top/bottom sales person per month

[#1]

Hi

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

[Carlos_Reyes · Accepted solution]

Is this what you want?

[#3]

That is amazing and exactly what I needed.

Thanks so much!!

James

[#4]

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

[#5]

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

[qliksus]

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.

[Carlos_Reyes]

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,

[#8]

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