Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
From the following script i am getting KPI values for september 2015 and october 2015 only because of completion date where condition.How to get july 2015 and august 2015 values as well. Can any one explain how to use dynamic variables (min and max months) for this.
//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)\WO.qvd (qvd) where year(floor(wo_completionDate))='2015'
and num(month(floor(wo_completionDate)),'00') >='07' and num(month(floor(wo_completionDate)),'00')<='10' ;
left join(Summary)
load
CompletionYear,
CompletionMonth,
[Client source],
wo_pk,
count(wo_number) as [No Of Work Orders]
resident Summary
group By
CompletionYear,
CompletionMonth,
[Client source],
wo_pk;
//KPI 1
left join(Summary)
load
CompletionYear,
CompletionMonth,
[Client source],wo_pk,
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 wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())
group by
CompletionYear,
CompletionMonth,
[Client source]
,wo_pk
;
//KPI 2
left join(Summary)
LOAD
CompletionYear,
CompletionMonth,
[Client source],wo_pk,
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 wo_completionDate >= Addmonths(monthStart(Today()),-1) and wo_completionDate <= monthEnd(Today())
group by
CompletionYear,
CompletionMonth,
[Client source]
,wo_pk
;
Best Regards,
Ganesh
Message was edited by: Ganesh Reddy
If you want two more months then change the -1 to -3 and you can leave out the Addmonths function since Monthstart also accepts an offset parameter:
and wo_completionDate >= monthStart(Today(),-3) and wo_completionDate <= monthEnd(Today())
Thank for the reply Gysbert, According to the requirement i need to use that where condition with -1 because
Wo_completionDate has to be between the beginning of prior month and the end of current month it is inevitable . The only implementation we can do here is calculating KPIs for each month separately and has add to summary table. Is there any way to use dynamic variable to achive this.
best regards,
Ganesh
The current month is the month of today, i.e. October. You'll never find data from august if the completion date must lie in September.
Yes Gysbert, because of that i am only getting data for sept and oct. For more reference please find the attached thread. Logic help . I have not got the result there. Here the basic idea is to calculate KPI for the month of July and add it to summary table and then for August and so on...
Best Regards,
Ganesh