3 Replies Latest reply: Sep 7, 2016 3:17 PM by m ellis RSS

    RE: Set Analysis as a script variable

    m ellis

      I prefer to define my variables within the script as much as possible for an easier life but I just cannot get this one to compute. I guess it's due to the double quote  v single quote can anyone help please? 

       

      =Sum({<SaleDate={"$(=Date(Max(SaleDate),'YYYY-MM'))-*"},StatusKey-={12},SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >} IsSale)

       

      Many Thanks

        • Re: RE: Set Analysis as a script variable
          Sunny Talwar

          Try this:

           

          LET vExp = '=Sum({<SaleDate={"$' & '(=Date(Max(SaleDate),' & Chr(39) & 'YYYY-MM' & Chr(39) & '))-*"},StatusKey-={12},SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >} IsSale)';

          • Re: RE: Set Analysis as a script variable
            Jonathan Dienst

            You need to defer the $ expansion so it is not evaluated during the script run, but in the front end. You can do this using a Let expression like Sunny's example. For complex expressions, I prefer to do this instead:

             

            ...

            Set vExp = =Sum({<SaleDate={"#(=Date(Max(SaleDate),'YYYY-MM'))-*"},StatusKey-={12},SaleFiscalYear=,SaleWeekDay=,SaleQuarter=, SaleMonth= >} IsSale);

            Set ...

            Set ...

            ...

            ...

            Let vExp = Replace(vExp, '#', '$');  // this does not trigger the expansion.

            Let ...

             

            This makes it easier to lay out complex expressions in a way that is easier to understand and maintain.

            • Re: RE: Set Analysis as a script variable
              m ellis

              That's a great Idea and certainly makes for more readable  and maintainable code. Further more the even more flexible if used to dynamically change  the field for aggregation. I'll try this out and let you know how I get on. In the meantime many thanks I appreciate  this "outside the box" solution.