topic Re: How to calculate KPI's with in Loop in Script Level ? in QlikView

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

madhubabum — Tue, 27 Oct 2015 18:55:06 GMT

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

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

maxgro — Tue, 27 Oct 2015 19:54:38 GMT

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;

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

madhubabum — Wed, 28 Oct 2015 06:45:15 GMT

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

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

sujeetsingh — Wed, 28 Oct 2015 06:48:16 GMT

what is the issue or error you have ?

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

madhubabum — Wed, 28 Oct 2015 06:56:24 GMT

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.