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 ?

[sunny_talwar · Accepted solution]

This:

Actual:

LOAD SalesmanID,

  Sales,

  Date#(YearMonth, 'YYYYMM') as YearMonth;

LOAD SalesmanID,

    YearMonth,

    Sales

FROM

Target.xlsx

(ooxml, embedded labels, table is Sheet1);

Target:

LOAD SalesmanID,

  Target,

  Date#(YearMonth, 'YYYYMM') as YearMonth;

LOAD SalesmanID,

    YearMonth,

    Target

FROM

Target.xlsx

(ooxml, embedded labels, table is Sheet1);

MaxDate:

LOAD Max(YearMonth) as MaxYearMonth

Resident Actual;

LET vMaxYearMonth = Peek('MaxYearMonth');

DROP Table MaxDate;

TempTable:

LOAD SalesmanID,

  Sum(Sales) as TotalSales

Resident Actual

Where YearMonth = $(vMaxYearMonth)

Group By SalesmanID;

Join(TempTable)

LOAD SalesmanID,

  Target

Resident Target

Where YearMonth = $(vMaxYearMonth);

Concatenate(Target)

LOAD SalesmanID,

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

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

Resident TempTable;

DROP Table TempTable;

[gautik92]

for salesmanid 1 the target is 40 for month 8 and he has achieved 50 then for month 9 his target should  be 50 right?

[gautik92]

Thanks sunindia‌ got the answer