topic Re: Target Setting in QlikView

Target Setting

gautik92 — Tue, 22 Sep 2015 06:42:05 GMT

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 ?

Re: Target Setting

gautik92 — Tue, 22 Sep 2015 09:13:34 GMT

jagan‌ sunindia‌

if you have any idea please share with me

Re: Target Setting

sunny_talwar — Tue, 22 Sep 2015 10:01:43 GMT

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

Re: Target Setting

gautik92 — Tue, 22 Sep 2015 10:14:48 GMT

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

Re: Target Setting

sunny_talwar — Wed, 23 Sep 2015 04:42:14 GMT

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:

Re: Target Setting

gautik92 — Thu, 24 Sep 2015 04:57:26 GMT

Thanks sunindia‌

Re: Target Setting

sunny_talwar — Thu, 24 Sep 2015 04:58:43 GMT

Not a problem

I hope this is what you were looking for.

Best,

Sunny

Re: Target Setting

gautik92 — Thu, 24 Sep 2015 05:03:08 GMT

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

Re: Target Setting

sunny_talwar — Thu, 24 Sep 2015 05:29:42 GMT

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

Re: Target Setting

gautik92 — Thu, 24 Sep 2015 06:05:51 GMT

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

Re: Target Setting

sunny_talwar — Thu, 24 Sep 2015 08:07:26 GMT

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;

Re: Target Setting

gautik92 — Thu, 24 Sep 2015 08:50:03 GMT

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?

Re: Target Setting

gautik92 — Thu, 24 Sep 2015 09:09:35 GMT

Thanks sunindia‌ got the answer