string variable in formulas

Tue, 24 Nov 2015 13:07:50 GMT

I have a table where I use the same expression

sum(if(NAME <> 'US Sales - Multi Year Portion' AND InMonth(STARTDATE,today(1),-x,1,0))

where "x" can be from 1 to 5, to show the leads that started in the last six months. I will probably have to add some other criteria

to this selection in the future, so I thought that if I could create a variable "vExpr1", that would only contain:

NAME <> 'US Sales - Multi Year Portion' AND InMonth(STARTDATE,today(1),

and then my formula for every column would be sum(if($(vExpr1)-x,1,0)

I could add another condition (say AND Status <> '019' ) by just changing the definition of vExpr1.

However, I tried

SET vExpr1 = 'NAME <> 'US Sales - Multi Year Portion' AND InMonth(STARTDATE,today(1),'

and that didn't work.

then I tried

SET vExpr1 = 'NAME <> '&CHR(39)&'US Sales - Multi Year Portion'&chr(39)&'AND InMonth(STARTDATE,today(1)';

and that didn't work either.

First, is this a good idea (using the variable instead of spelling out each formula)

Second: If yes, what's the best way to do this?

thanks,

Kevin

Re: string variable in formulas

swuehl — Tue, 24 Nov 2015 13:11:47 GMT

Try this for the variable definition:

SET vExpr1 = NAME <> 'US Sales - Multi Year Portion' AND InMonth(STARTDATE,today(1),;

Tue, 24 Nov 2015 13:16:03 GMT

thanks, Stefan. Worked perfectly!