1 Reply Latest reply: Mar 8, 2017 4:18 AM by Marcus Sommer RSS

    Nested variables in expression with greater than condition

    Alejandro Diego

       

      Hi,
      I have been fighting for a couple of days with a problem when using nested variables in an expression with a "greater than" condition. I have been searching in the forum and I have found similar problems but I have not been able to solve mine.

      My problem is the following: I have a variable in which I calculate the previous week to the day selected in a calendar. The variable will have the format year+week
      set v_1_Week_Ago_YYYYWW = Year( CalendarDate-7 ) & repeat('0', (2 - len(Week( CalendarDate-7 )))) & Week( CalendarDate-7 );

      Using this variable I create another which I pretend to be part of the filter of an expression:
      set v_filter_test = 'CalendarYearWeek={"=$(v_1_Week_Ago_YYYYWW)"}';

      and finally I have a last variable that will contain the expression:
      set v_value=sum({$<RecordType={C}, $(v_filter_prueba), CalendarDate=>} Transactions);

      The reason for all this is that, when I get this work, the final expression will be more complex, and the filters will be different depending on the selections.

      Well, if I use this variable in my report I get the right value, but if instead of
      set v_filter_test = 'CalendarYearWeek={"=$(v_1_Week_Ago_YYYYWW)"}';
      I use set v_filter_test = 'CalendarYearWeek={">$(v_1_Week_Ago_YYYYWW)"}';
      that is, I use a "greater than" condition instead of a "equal" I get the value "0"

      The idea is to get the amount between two weeks, so at the end the expression should be something like this:
      v_filter_test = 'CalendarYearWeek={">$(v_4_Week_Ago_YYYYWW) <=$(v_1_Week_Ago_YYYYWW)"}';

      I have even tried to use a different simbol instead of the '$' and then perform a replace. I think I have tried everything but the correct approach.

      Many thanks in advance

       

        • Re: Nested variables in expression with greater than condition
          Marcus Sommer

          Try to create a numeric variable and of course field, for example by using something like this:

           

          set v_1_Week_Ago_YYYYWW = Year(CalendarDate-7) * 100 + Week(CalendarDate-7);

           

          Beside them I'm not sure if you aren't overused variables which should make things easier and not adding more complexity. By developing complex and nested variables it's often useful to use a reverse approach and creating a working expression manually and then replacing the various parts step by step with the variables.

           

          - Marcus