1 Reply Latest reply: May 2, 2013 12:47 PM by Gysbert Wassenaar RSS

    Time Dimension, Value N/N-1

      Hi everyone,

       

      I am stucked with a problem I can't solve. Let me explain you.

      I have quite a simple fact :

      • Year
      • Month
      • Period (Month + Year)
      • € turnover

       

      All I want is a table showing :

       

       

      my dimension -> (period)
      Jan 2012
      Feb 2012
      Mar 2012
      ...
      turnover100200250..
      turnover (last year)90100150..

       

       

      I tried these two expressions :

      • SUM(€ turnover)
      • SUM({$<Year={$(=Only(Year)-1)}>}

       

      But unfortunately, all I manage to get is a table with these dimensions : Jan 2011, Feb 2011, Mar 2011 (with 0 in the turnover expression, and the correct value in the turnover (last year) expression), Jan 2012, Feb 2012, Mar 2013 (with the correct value in the turnover expression and 0 in the turnover (last year) expression).

       

      Do anyone have a clue about this ?

       

      Thanks in advance,

       

      Regards,

       

      Damien

        • Re: Time Dimension, Value N/N-1
          Gysbert Wassenaar

          A clue? Sure, set analysis expressions calculates a set per chart, not per row. When you use period as a dimension in your chart you won't get a set for each period. That's why SUM({$<Year={$(=Only(Year)-1)}>} doesn't work. You need to select 1 year or only(Year) doesn't return a value. And if you select 1 year then the turnover (last year) values are displayed in the year before, not the selected year.

           

          What you can do is join the table in the script with a new table so every period is associated with the period of the previous year. Something like:

           

          T1:

          load Year, Month, Month&Year as Period, turnover

          from ...somewhere...;

           

          join load

          (Year+1) as Year,

          Month, Month&(Year+1) as Period, turnover as turnover_last_year