Discussion board where members can get started with QlikView.
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
Go to 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', '.', ',')
View solution in original post
try like this :
PFA attached qvw
You can modify as required.
Thanks for replying but I'm using the PE so cant open the qvw file.
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.