Target Setting

[gautik92]

I have a Scenario like i want to set target for each salesman considering the acheivement of last month if the achievement is more than target of last month i want to set that as target for this month and if achievement is less have to add some percentage to set target for current month.

How can i achieve this ?

[gautik92]

jagan‌ sunindia‌

if you have any idea please share with me

[sunny_talwar]

Is there a small sample you would be able to share?

[gautik92]

I don have sample data

actually the requirement is every month first week we have to set target for every salesman

comparing the target and acheievement of last month if the achievement of last month is greate when compared to previous months then achievement of last month will be the target of current month if last month achievement is less when compared to other months then 5-10% will be added to the last month achievement values

[sunny_talwar]

Not sure how your data looks, but tried to build a sample for you. See if this is close to what you want:

Actual:

LOAD SalesPerson,

  Sales,

  Date#(YearMonth, 'YYYYMM') as YearMonth

Inline [

SalesPerson, Sales, YearMonth

A, 40, 201507

A, 30, 201507

A, 40, 201508

A, 20, 201508

A, 50, 201508

B, 60, 201507

B, 90, 201508

B, 20, 201508

B, 10, 201508

];

Target:

LOAD SalesPerson,

  SalesTarget,

  Date#(YearMonth, 'YYYYMM') as YearMonth

Inline [

SalesPerson, SalesTarget, YearMonth

A, 50, 201507

A, 115, 201508

B, 50, 201507

B, 115, 201508

];

MaxDate:

LOAD Max(YearMonth) as MaxYearMonth

Resident Actual;

LET vMaxYearMonth = Peek('MaxYearMonth');

DROP Table MaxDate;

TempTable:

LOAD SalesPerson,

  Sum(Sales) as TotalSales

Resident Actual

Where YearMonth = $(vMaxYearMonth)

Group By SalesPerson;

Join(TempTable)

LOAD SalesPerson,

  SalesTarget

Resident Target

Where YearMonth = $(vMaxYearMonth);

Concatenate(Target)

LOAD SalesPerson,

  If(TotalSales >= SalesTarget, SalesTarget, SalesTarget * (1.10)) as SalesTarget,

  Date(AddMonths($(vMaxYearMonth), 1), 'YYYYMM') as YearMonth

Resident TempTable;

DROP Table TempTable;

Output:

[gautik92]

Thanks sunindia‌

[sunny_talwar]

Not a problem

I hope this is what you were looking for.

Best,

Sunny

[gautik92]

Hi sunny ,

Actually i want to set target for current month comparing the sales of previous month i think in the solution yu have don have target setting

[sunny_talwar]

I guess the same idea should follow, but having said that, until I see some sample data, it would be hard to suggest anything.

[gautik92]

PFA

there is sales and target for the months 6 ,7 and 8 i want to set target for the month 9 comparing the sales and target of the salesman