Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am stucked with a problem I can't solve. Let me explain you.
I have quite a simple fact :
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 :
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
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
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