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
      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,





        • 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:



          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