Time Dimension, Value N/N-1

[#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	100	200	250	..
turnover (last year)	90	100	150	..

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

[Gysbert_Wassenaar · Accepted solution]

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