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?

       

      thanks,

       

      Kevin