Variable troubles

[#1]

Alright chaps.

I have a variable...

LET vMaxdate = '=max(MonthStart(prvMonthEnd))';

In a for loop I want to end up with...

vMaxdate_1=AddMonths('$(vMaxdate)',-11)

vMaxdate_2=AddMonths('$(vMaxdate)',-10)

vMaxdate_3=AddMonths('$(vMaxdate)',-9) etc.

FOR a = 1 to 12

  LET b = 12-$(a);

  LET vMaxdate_$(a) = '=AddMonths($(vMaxdate),-$(b))';

NEXT a;

But I am getting the text of vMaxdate in each variable...

vMaxdate_1=AddMonths(=max(MonthStart(prvMonthEnd)),-11)

vMaxdate_2=AddMonths(=max(MonthStart(prvMonthEnd)),-10)

vMaxdate_3=AddMonths(=max(MonthStart(prvMonthEnd)),-9) etc.

So how do I force the variable name rather than its expression into the second set of variables?

Thanks.

Foldy

[alexandros17]

I should try this:

LET vMaxdate = max(MonthStart(prvMonthEnd));

FOR a = 1 to 12

  LET b = 12-$(a);

  LET vMaxdate_$(a) = AddMonths($(vMaxdate),-$(b));

NEXT a;

[#3]

Thanks but that's not quite what I wanted. I think that will try and evaluate the AddMonths whereas I want the AddMonths as text along with the variable $(vMaxdate)...

[MarcoWedel]

Hi foldyman,

you might also consider using a single parameterized variable instead of the whole creating multible variables in a loop concept:

SET vMaxdate = '=AddMonths(max(MonthStart(prvMonthEnd)),'&$1&'-12)';

which could be used like

$(vMaxdate(1))  for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-11)'

$(vMaxdate(2))  for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-10)'

$(vMaxdate(3))   for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-9)'

...

which then could be calculated using this syntax:

$(=$(vMaxdate(1)))

hope this helps

regards

Marco

[maxgro · Accepted solution]

SCRIPT

LET vMaxdate = 'max(MonthStart(prvMonthEnd))';

FOR a = 1 to 12

  LET b = 12-$(a);

  LET vMaxdate_$(a) = '=AddMonths(' & chr(39) & '$' & '(vMaxdate)' & chr(39) & ',-$(b))';

NEXT a;

RESULT

[#6]

Thanks Marco. Never seen parameterized variables before. I tried it out on my dash but it seems in this instance that performance really suffers. I'll be keeping the idea in my code snippets bank for the future tho. Thanks.

[#7]

Thanks Massimo, that seems to have done it though I didn't need the chr(39)'s. But...

why doesn't the first line evaluate the same as the second?

	'=AddMonths($(vMaxdate),-$(b))';
	'=AddMonths(' &  '$' & '(vMaxdate)'  & ',-$(b))';

It seems to me (a .net developer) that they should be the same as the second is just concatenated pieces of text?

[MarcoWedel]

interesting, I never noticed any performance influence of this.

Did you intentionally mark your own comment helpful?

😉

regards

Marco

[#9]

Errr yes. obviously getting click happy in my old age. Unmarked now tho.

Perhaps I was doing something wrong - I'll try it again. Thanks.