4 Replies Latest reply: Jul 8, 2014 5:24 AM by sasi k RSS

    Variables in Set Analysis

    Matthias Karl

      Hi

       

      My expression

       

      Sum({$<Debug={'Budget'}, MonthSort = {'<=3'}>} If(AC2='70', BalanceLC))

       

      works like a charm, summing up all values where the month is smaller than 3. Now I would like to replace the 3 with a variable, but I can't get it to work.

       

      In the load script I have defined

       

      LET vPeriods = 3;

       

      Now when I try to replace the 3 with vPeriods it doesn't show any values. How can I use the variable in this expression?

       

      Using CTRL+ALT+V shows that the variable exists and has the proper value:

       

       

      I have tried:

       

      Sum({$<Debug={'Budget'}, MonthSort = {'<=vPeriods'}>} If(AC2='70', BalanceLC)) = All values below limit

      Sum({$<Debug={'Budget'}, MonthSort = {'<='&$(vPeriods)}>} If(AC2='70', BalanceLC)) = No data to display

      Sum({$<Debug={'Budget'}, MonthSort = {'<='$(vPeriods)}>} If(AC2='70', BalanceLC)) = No data to display

      Sum({$<Debug={'Budget'}, MonthSort = {$('<='&vPeriods)}>} If(AC2='70', BalanceLC)) = All values below limit

      Sum({$<Debug={'Budget'}, MonthSort = {"<=vPeriods"}>} If(AC2='70', BalanceLC)) = All values below limit

       

      What am I doing wrong?

       

      The following is used to create the field MonthSort (cos our Fiscal Year starts in April):

       

      LOAD * INLINE

      [

      MonthName, MonthSort, FMonth

      APR, 1, 01

      MAY, 2, 02

      JUN, 3, 03

      JUL, 4, 04

      AUG, 5, 05

      SEP, 6, 06

      OCT, 7, 07

      NOV, 8, 08

      DEC, 9, 09

      JAN, 10, 10

      FEB, 11, 11

      MAR, 12, 12

      ];