Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## For loop increment by every month

hello guys ,

I need Value 12 for 1st four months and then another set of value  from 2nd months onwards and so on as shown below .

I need to autogenerate value 12 four times every coming month and so on till 20 months .

 Months 1 2 3 4 5 6 7 8 9 10 1 12 2 12 12 3 12 12 12 4 12 12 12 12 5 12 12 12 6 12 12 7 12 8 12 9 12 10 12 12

Here is my script it does not give me desired results . Could u plz help me with solution .  When i use Value and Month in the chart i will get value= 12 for 20 months . But i need something as shown in the matrix above .

FOR i = 1 TO 20

Let Duration=i;

[XYZ]:

'12' as Value,

'Month' & \$(Duration) as Month

AutoGenerate 1;

NEXT i

1 Solution

Accepted Solutions
Champion

here is the new code:

[DataNew1]:

LOAD * Inline [

"Impact month"

12

];

let vImpact_Month= Peek('Impact month',0,DataNew1);

TRACE \$(vImpact_Month);

for i=1 to 10

for x=1 to 4

if \$(i) =1 then

test:

\$(i) as Month,

\$(x) as Month2,

\$(vImpact_Month) as Value

AutoGenerate 1;

ELSE

test:

\$(i) as Month,

\$(x)+(\$(i)-1) as Month2,

\$(vImpact_Month) as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;

14 Replies
Champion

Hi,

Try this:

for i=1 to 10

for x=1 to 4

if \$(i) =1 then

test:

\$(i) as Month,

\$(x) as Month2,

12 as Value

AutoGenerate 1;

ELSE

test:

\$(i) as Month,

\$(x)+(\$(i)-1) as Month2,

12 as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;

==> create a pivot table with Month and Month1 as dimensions and sum(value) as expression

Anonymous
Not applicable
Author

Thank you very much ! It worked like a charm

i want to do a resident load . But i am not able to put it .

Kindly let me know where do I put

Resident [DataNew1];      in the script .

Thanks,

Ravi

Champion

Example for RESIDENT:

TABLE1:

from...

TABLE2:

RESIDENT TABLE1;

==> the only condition is to load a table already loaded on the model.

Anonymous
Not applicable
Author

Thanks for reply !

Yes i have a loaded table which is [DataNew1]

Now i want to Resident this in the script what you suggested but I am not able to put   Resident [DataNew1];  in the script .

Where should i write Resident [DataNew1];  in the above script ? I mean after End if or after Autogenerate or ....?

Champion

I used Autogenerate because I started with your script..

but you can't use resident with autogenerate.

you must know: if you want to generate the MONTHS/Value table or the table already exists on the model.

the best you can do is to attach your App if there is no private data.

Anonymous
Not applicable
Author

Hello ,

[DataNew1]:

"Product Line",

"Impact month",

FROM [lib://AttachedFiles/Complexity_Mix.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Model);

For i=1 to 10

For x=1 to 4

if \$(i) =1 then

test:

\$(i) as Month,

\$(x) as Month2,

"Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

//           12 as Value

AutoGenerate 1;

ELSE

test:

\$(i) as Month,

\$(x)+(\$(i)-1) as Month2,

"Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

//           12 as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;

So how do I get this "Impact month"  field in the loop ?

Champion

I should modify the script to add another column with an autogenerate..

can you show what is the expected result with this new column ?

because I'm already generating 2 months columns

Anonymous
Not applicable
Author

Ok !

If you see initially what we did in the script is

'' 12 as Value ''

This value 12 is actually in the field named as [Impact Month ]

So rather than mentioning 12 as Value  , I wanted to  include Field [Impact Month ] in the script .

Bcoz now it has value 12 , 2moro it may have some other value so as to make it more dynamic .

And [Impact Month ] is in the 1st Table named as [DataNew1]:

So was trying to use Resident , But i am not able to

Thnx

Champion

here is the new code:

[DataNew1]:

LOAD * Inline [

"Impact month"

12

];

let vImpact_Month= Peek('Impact month',0,DataNew1);

TRACE \$(vImpact_Month);

for i=1 to 10

for x=1 to 4

if \$(i) =1 then

test:

\$(i) as Month,

\$(x) as Month2,

\$(vImpact_Month) as Value

AutoGenerate 1;

ELSE

test:

\$(i) as Month,

\$(x)+(\$(i)-1) as Month2,

\$(vImpact_Month) as Value

AutoGenerate 1;

ENDIF

NEXT x;

NEXT i;

Tags
Community Browser