Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 has answered your question -- the full expression:
='Top sales person - ' & FIRSTSORTEDVALUE(SalesPerson, -AGGR(SUM(SaleAmount),SalesPerson)) & ' - ' & Num(Max(AGGR(SUM(SaleAmount),SalesPerson)), '$#,##0', '.', ',')
Try like:
Max(AGGR(SUM(SaleAmount),SalesPerson))
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
Thanks for replying but I'm using the PE so cant open the qvw file.
tresesco has answered your question -- the full expression:
='Top sales person - ' & FIRSTSORTEDVALUE(SalesPerson, -AGGR(SUM(SaleAmount),SalesPerson)) & ' - ' & Num(Max(AGGR(SUM(SaleAmount),SalesPerson)), '$#,##0', '.', ',')
use this :
='Sales person name -'&' '& SubField(concat ( ( Sales_Person),'@',1/SaleAmount ),'@',1) &' '&'Amount is '&':' & SubField(concat ( ( Aggr(sum(SaleAmount),Sales_Person) ),'@',1/SaleAmount),'@',1)
Thanks guys...both of these solutions worked.
I'll have to spend a bit more time looking at the AGGR function.