how can i create this pivot?

[amien]

table looks like this:

id, date, profit, costs, discount

pivot should look like this:

                   year = 2010 |  year = 2011   |  grow %

-------------------------------------------------------------------

profit         | expression   |  expressions   |  expression

costs        |                       |                           |

discount  |                        |                            |  

i can put year in dimensions, add serval expressions (profit, costs, discount) and than drag the expressions to the dimension location and the dimension location to the top. works great, but than i cant add the % column .. how can this be designed?

[stephencredmond]

There are several ways of doing this.  Depending on your requirements, you might want to look at a set.  However, the easiest way is that you keep the chart that you have with Year pivoted across the top and a 2nd expression for the growth using the Before function.  E.g.:

(Sum(LineSalesAmount)-Before(Sum(LineSalesAmount)))/Sum(LineSalesAmount)

In the first column, this value will be null (because there is no "Before") but it will calculate for other years.  This allows you to have several years in your pivot and display the growth for all of them.

Regards,

Stephen

[stephencredmond]

Ah!

Just re-read your original and see that you already have several expressions and have pivoted them.

Unfortunately you can't pivot individual expressions - you have to do them all.  But you can still use my expression, just one for each calculation and then you have the growth figures under each of the other expressions rather than to the side.

Regards,

Stephen

[amien]

i solved this with reading the source data with a CROSSTABLE function.