Top sales person region wise

[#1]

Hi All,

I wan to calculate Top sales person region wise based on YTD values.

In the attached qvw sample, if I select Year 2015 and Month March, I want to get the Top sales person & Values based on Jan-2015 to Mar-2015 (aggregated value).

I have attached sample qvw file with data

Expected output for Year-2015 & Month-Mar is below

Region	Top 1 Sales Person	Top 2 Sales Person	Top 3 Sales Person
East	E : 570	D : 550	F : 525
West	C: 565	A: 550	B: 500

Note that there could be 4 regions and many sales persons in real scenario. Also note that I want to get this done from front end only.

[Kushal_Chawda · Accepted solution]

see the attached

[#3]

Hi,

My first reply so apologies if this doesn't go to plan but does the following give you any success...

'=only((if(aggr(Rank(sum(Amt)),SalesPerson)=1,SalesPerson)))

Fingers crossed,

Jason.

[sunny_talwar]

Try this:

Script change:

LOAD *, year(MonthYear) as Year,

month(MonthYear) as Month;

LOAD *,Date(Date#(YearMonth,'MMM-YYYY')) as MonthYear Inline [

Region, SalesPerson, YearMonth, Amt

West, A, Jan-2015,100

West, A, Feb-2015,150

West, A, Mar-2015,300

West, A, Apr-2015,210

West, B, Jan-2015,170

West, B, Feb-2015,110

West, B, Mar-2015,220

West, B, Apr-2015,70

West, C, Jan-2015,250

West, C, Feb-2015,160

West, C, Mar-2015,155

West, C, Apr-2015,200

East, D, Jan-2015,110

East, D, Feb-2015,230

East, D, Mar-2015,210

East, D, Apr-2015,100

East, E, Jan-2015,120

East, E, Feb-2015,240

East, E, Mar-2015,210

East, E, Apr-2015,70

East, F, Jan-2015,250

East, F, Feb-2015,135

East, F, Mar-2015,140

East, F, Apr-2015,290 ];

Expression:

=Sum({<MonthYear = {"$(='>=' & Date(YearStart(Max(MonthYear))) & '<=' & Date(MonthEnd(Max(MonthYear))))"}, Month =, Year = >}Amt)