KPI Calculation with For Loop

madhubabum — Wed, 28 Oct 2015 07:08:58 GMT

Hi Experts

The Following 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 .

Note :Extraction should start from 2014 onwards

Please find the bellow code.

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 CompletionMonth >= $(i) and CompletionMonth >= $(i)-1
and CompletionYear >= $(j)
group by

[Client source]
;
next
next;

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

Thanks

Madhu

topic KPI Calculation with For Loop in QlikView

KPI Calculation with For Loop