Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Displaying Total Sales for Top SalesPerson In Text Box

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Displaying Total Sales for Top SalesPerson In Text Box

tresesco has answered your question -- the full expression:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
MVP
MVP

Re: Displaying Total Sales for Top SalesPerson In Text Box

Try like:

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

balkumarchandel
Valued Contributor II

Re: Displaying Total Sales for Top SalesPerson In Text Box

Hi ,

try like this :

Sample data

  

Sales_PersonProductRegionSaleAmount
S1P1US100
S1P1UK100
S1P2UK100
S2P1UK200
S2P2US200
S2P1US200

PFA attached qvw

You can modify as required.

Thanks

BKC

Not applicable

Re: Displaying Total Sales for Top SalesPerson In Text Box

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

MVP
MVP

Re: Displaying Total Sales for Top SalesPerson In Text Box

tresesco has answered your question -- the full expression:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

balkumarchandel
Valued Contributor II

Re: Displaying Total Sales for Top SalesPerson In Text Box

use this :

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

Not applicable

Re: Displaying Total Sales for Top SalesPerson In Text Box

Thanks guys...both of these solutions worked.

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