How to calculate KPI's with in Loop in Script Level ?

[madhubabum]

Hi Experts,

--> I have WorkOrderdate & WorkOrderCompletionDate Fields

--> I have date from May to Till Date

Note : I want to calculate KPI'S as following in Script level  (With loop condition)

Note : First we need to calculate KPI 1 then KPI 2 Then KPI 3 then KPI 4

KPI 1:

-->I want to Calculate  WorkorderLaborHours From June to July

KPI 2:

-->I want to Calculate  WorkorderLaborHours From July to August

KPI 3:

-->I want to Calculate  WorkorderLaborHours From August to September

KPI 2:

-->I want to Calculate  WorkorderLaborHours From September to Current Date

I am attaching Excel file and Application ..

How we can implement this one ?

Thanks

[maxgro]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

WO:

load

  *,

  Year(WorkOrderCompletionDate) as Year,

    month(WorkOrderCompletionDate) as Month,

    WorkOrderCompletionDate-WorkOrderDate as DIFF;

LOAD ID,

     WorkOrderLabourHours,

     Date(date#(WorkOrderDate, 'DD/MM/YYYY')) as WorkOrderDate,

     Date(date#(WorkOrderCompletionDate, 'DD/MM/YYYY')) as WorkOrderCompletionDate    

     //floor(WorkOrderCompletionDate)-floor(WorkOrderDate) as wo_Diff

//     date((date(WorkOrderCompletionDate,'MM/DD/YYY')-date(WorkOrderDate,'MM/DD/YYYY')),'DD') as DIFF

FROM

OPT_Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(WO)

LOAD

sum(WorkOrderLabourHours) as TotalLabor_Hours

Resident WO;

for i=1 to 4

  KPI:

  load

  'KPI' & $(i) as KPI,

  date(min(WorkOrderDate)) as MinDate,

  date(max(WorkOrderDate)) as MaxDate,

  sum(WorkOrderLabourHours) as SumLabour

  Resident WO

  Where

  WorkOrderDate >= makedate(2015, $(i)+5)

  and WorkOrderDate < makedate(2015, $(i)+7)

  ;

next; 

[madhubabum]

Hi Maxgro

Thanks for your reply

I am totally miss guided you , Extremely sorry for that.

I am try to achieve the  following code in my Application for Calculating KPI's

It is not working now , what we are trying to achieve  is Two Months Workorders

i.e This month and Previous month will fall in this month only. This is only because of Business Requirement .

Please find the bellow code and guide me .

Code :

Summary:
Load *, floor(wo_completionDate)-floor(wo_requestDate) as wo_Diff,
fd_code as [Client source],
year(floor(wo_completionDate)) as CompletionYear,
num(month(floor(wo_completionDate)),'00') as CompletionMonth,
year(floor(wo_createdDate))&'-'&num(month(floor(wo_createdDate)),'00') as CreatedYearMonth,
year(floor(wo_requestDate))&'-'&num(month(floor(wo_requestDate)),'00') as RequestYearMonth,
year(floor(wo_completionDate))&'-'&num(month(floor(wo_completionDate)),'00') as CompletionYearMonth1
from $(vQVDDataDir)\Optimization_WO.qvd (qvd) where year(floor(wo_completionDate))='2015' and year(floor(wo_completionDate))='2014';
//and num(month(floor(wo_completionDate)),'00') >='07' and num(month(floor(wo_completionDate)),'00')<='10' ;

let vMinMonth = Min(CompletionMonth);
let vMaxMonth = Max(CompletionMonth);
let vMinYear = Min(CompletionYear);
let vMaxYear = Max(CompletionYear);

//KPI 1
for j=$(vMinYear) to $(vMaxYear)
for i=$(vMinMonth) to $(vMaxMonth)

left join(Summary)
load
[Client source],
count(wo_number) as [No Of Work Orders 1]
resident Summary where pri_code= 1 and  wc_code = '01'
and wo_Diff <=1
and isnull(proj_number)
and CompletionMonth >= $(i) and CompletionMonth >= $(i)-1
and CompletionYear >= $(j)

// and wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())
     group By
[Client source]
;
next
next;


//KPI 2
for j=$(vMinYear) to $(vMaxYear)
for i=$(vMinMonth) to $(vMaxMonth)

left join(Summary)
LOAD

[Client source],
count(wo_number) as [No Of Work Orders 1.1]
resident Summary  where pri_code= 1 and wc_code = '01'
and isnull(proj_number)
and CompletionMonth >= $(i) and CompletionMonth >= $(i)-1
and CompletionYear >= $(j)
group by
[Client source]

;
next
next;     

//KPI 3
for j=$(vMinYear) to $(vMaxYear)
for i=$(vMinMonth) to $(vMaxMonth)

left join(Summary)
load

[Client source],
count(wo_number) as [No Of Work Orders 2]
resident Summary  where pri_code= 2 and wc_code = '01'
and wo_Diff <=2
and isnull(proj_number)
and wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())
group by 

[Client source]
;
next
next;

STORE Summary INTO [$(vQVDDataDir)\Optimization_Summary.qvd] (qvd);

Thanks

Madhu

[sujeetsingh]

what is the issue or error you have ?

[madhubabum]

Hi sujeet,

For loop syntax and variable calling are the issues here. Please find the above code it is throwing garbage statement errors and for loop syntax errors.

Best Regards,

Madhu.