Merge cells / Adding a row to Straight Table

[eliran]

Hi,

I've got a straight table and I want to adjust it a little bit.

My current straight table looks like this:

2009 qty, 2009 sales, 2009 avg, 2010 avg, 2010 sales, 2010 avg

Dimension:

I want to make something like this:

2009 2010

qty | sales | avg qty | sales | avg

Dimension:

Is it possible to do something like this without touching the data? is it possible at all?

I'm afraid I saw some old posts regarding this subject but they had no answers.

Thanks!

[#2]

Not with a straight table, but you should be able to do it with a pivot. Convert your straight table to a pivot and add Year as the last dimension. Then drag that dimension to the top. In your table, you had 6 expressions, you will only need three on the updated pivot.

[eliran]

Thanks for the quick reply.

I'm afraid it's a bit more complicated.

All these columns are being calculated with set analysis and the idea is to choose a specific year and according to the selection to take 3 years back and calculate.

In addition there are more fields that are being calculated without the need of the year.

Is there a way I can insert a simple text row so I can take a formula like max(year)-1 and put it there, thus getting the required design?

Regards.

[pover]

There is no direct way to do this that I know of, but what you can do is make the column header 3 rows tall in the Presentation tab and then in the sales column label put ='2009'&chr(13)&chr(13)&'sales' while deleting the other references to the year in the other labels. Then put a blank expression (='') between avg 2009 and qty 2010 to add space.

Regards.

[johnw]

Eliran.S wrote:
All these columns are being calculated with set analysis and the idea is to choose a specific year and according to the selection to take 3 years back and calculate.
In addition there are more fields that are being calculated without the need of the year.

Consider generating an AsOf table:

AsOfYear, Year
2010, 2010
2010, 2009
2010, 2008
2009, 2009
2009, 2008
2009, 2007
etc.

If the user selects 2010, that will then map to three separate years. Include Year as the dimension in the pivot table, and you can do your calculations without set analysis. For columns in the table that you don't want to depend on the year, probably use calculated dimensions instead of expressions? Not sure. What are the other columns that you're calculating?