Dynamic variables

[ganeshreddy]

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

[Gysbert_Wassenaar]

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())

[ganeshreddy]

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

[Gysbert_Wassenaar]

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.

[ganeshreddy]

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