2 Replies Latest reply: Nov 24, 2015 8:16 AM by Kevin Bertsch RSS

    string variable in formulas

      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?