Announcements
cancel
Showing results for
Did you mean:
Creator III

## Loop through months in every year.

Hi All,

Here i am calculating metrics by looping through months in every year (Ex : i got data from 1977 to 2018) , below code is not working . can anyone suggest how to increment year value correctly.

Note: i need to use minMonth and maxMonth due to where condition

Temp_month:

num(max(Month(wo_completionDate))) as maxMonth,

min(year(wo_completionDate)) as minYear,

max(year(wo_completionDate)) as maxYear

let vMinMonth= peek('minMonth');

let vMaxMonth= peek('maxMonth');

let vMinYear=peek('minYear');

let vMaxYear=peek('maxYear');

let i=\$(vMinMonth);

let j=\$(vMinYear);

do while i<=\$(vMaxMonth) and j<=\$(vMaxYear)

join(Summary)

'\$(i)' as Month,

'\$(j)' as Year,

fd_code as [Client source],

count(if(code=1 and  wc_code = '01' and  Diff <=1 , wo )) as CorrWoPriority1Num,

count(if(pricode=1 and  wc_code = '01'  ,wo_number)) as CorrWoPriority1Den,

count(if(code=2 and  wc  = '01' and  Diff <=2,wo )) as CorrWoPriority2Num,

count(if(code=2 and  wc  = '01' ,wo_number)) as CorrWoPriority2Den,

count(if(code=3 and  wc = '01' and  Diff <=28,wo )) as CorrWoPriority3Num,

count(if(code=3 and  wc = '01'  ,wo_number)) as CorrWoPriority3Den,

count(if(ode=4 and  wc  = '01' and  Diff <=14 ,wo )) as CorrWoPriority4Num,

count(if(code=4 and  wc  = '01'  ,wo )) as CorrWoPriority4Den

where num(month(floor(wo_completionDate))) >= '\$(i)'-1 and num(month(floor(wo_completionDate))) <= '\$(i)'

and isnull(proj_number)

group By

fd_code;

Let i=i+1;

let j=j+1;

LOOP

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

Best Regards,

Ganesh

4 Replies
Specialist

Hi Ganesh,

please explain more what is the problem you are trying to solve here, what is the requirement.

Also you should verify your code because it seems to be that some fields are named incorreclty, like this:

count(if(ode=4 and  wc  = '01' and  Diff <=14 ,wo )) as CorrWoPriority4Num,

Kindest regards,

Santiago Respane

MVP

Hi Ganesh, weird way to do the loop, you can't add '1' to 'i' and 'j' each iteration:

if this starts with 01/1977 i=1, J=1977, next iteration will be i=2, J=1978

following this approach you can check when to add 1 to j:

Try Changing:

Let i=i+1;

let j=j+1;

To:

Let j=If(i=12, j+1, j);

Let i=If(i=12, 1, i+1);

Another thing, in the where you're using >= and <=, so each iteration will have 2 months loaded, maybe you need to remove the '=' in one of the conditions.

Master

HI ,

Try some thing like below

Use FOR loop here, if Min month and max month are available you can use them or

else create them like

vMinMonth=1;

vmaxMonth=12;

FOR i=\$(vMinYear)-1 to \$(vMaxYear)-1

FOR j=\$(vMinMonth) to \$(vMaxMonth)

\$(J) as Month,

\$(i)+1 as Year,

fd_code as [Client source],

.

.

.

.

NEXT

NEXT

MVP

btw, you also need to change the while conditions to:

do while j<\$(vMaxYear) or (i<=\$(vMaxMonth) and j=\$(vMaxYear))

Community Browser