Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
what is the issue or error you have ?
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.