Displaying Total Sales for Top SalesPerson In Text Box

[#1]

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

[tresesco]

Try like:

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

[Anonymous]

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

[#4]

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

[jonathandienst · Accepted solution]

tresesco has answered your question -- the full expression:

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

[Anonymous]

use this :

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

[#7]

Thanks guys...both of these solutions worked.

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