topic Displaying Total Sales for Top SalesPerson In Text Box in QlikView

Displaying Total Sales for Top SalesPerson In Text Box

Tue, 12 May 2015 11:28:26 GMT

I want to display the total sales for the top sales person, total sales for the top performing region and total sales for the top performing product

I can use an expression in a text box to display the name of the sales person and the name of the region but I'd like also to display the amount of sales

( FIRSTSORTEDVALUE((SalesPerson, -AGGR(SUM(SaleAmount),SalesPerson)))

( FIRSTSORTEDVALUE((SalesRegion, -AGGR(SUM(SaleAmount),SalesRegion)))

( FIRSTSORTEDVALUE((Product, -AGGR(SUM(SaleAmount),Product)))

The problem I think is that I don't have the sales amounts aggregrated across the Sales Person, Region and Product and was wondering if there was a way to display e.g.

Top Sales Person - Sale Person Name - $20,000

Top Region - USA - $100000

Top Product - ProductBrandName - $54,000

Re: Displaying Total Sales for Top SalesPerson In Text Box

tresB — Tue, 12 May 2015 11:33:51 GMT

Try like:

Max(AGGR(SUM(SaleAmount),SalesPerson))

Re: Displaying Total Sales for Top SalesPerson In Text Box

Anonymous — Tue, 12 May 2015 12:21:08 GMT

Hi ,

try like this :

Sample data

Sales_Person	Product	Region	SaleAmount
S1	P1	US	100
S1	P1	UK	100
S1	P2	UK	100
S2	P1	UK	200
S2	P2	US	200
S2	P1	US	200

PFA attached qvw

You can modify as required.

Thanks

BKC

Re: Displaying Total Sales for Top SalesPerson In Text Box

Tue, 12 May 2015 12:38:34 GMT

Thanks for replying but I'm using the PE so cant open the qvw file.

Re: Displaying Total Sales for Top SalesPerson In Text Box

jonathandienst — Tue, 12 May 2015 12:46:58 GMT

tresesco has answered your question -- the full expression:

='Top sales person - ' & FIRSTSORTEDVALUE(SalesPerson, -AGGR(SUM(SaleAmount),SalesPerson)) & ' - ' & Num(Max(AGGR(SUM(SaleAmount),SalesPerson)), '$#,##0', '.', ',')

Re: Displaying Total Sales for Top SalesPerson In Text Box

Anonymous — Tue, 12 May 2015 13:47:00 GMT

use this :

='Sales person name -'&' '& SubField(concat ( ( Sales_Person),'@',1/SaleAmount ),'@',1) &' '&'Amount is '&':' & SubField(concat ( ( Aggr(sum(SaleAmount),Sales_Person) ),'@',1/SaleAmount),'@',1)

Re: Displaying Total Sales for Top SalesPerson In Text Box

Tue, 12 May 2015 15:46:00 GMT

Thanks guys...both of these solutions worked.

I'll have to spend a bit more time looking at the AGGR function.