comparision in Crosstab

[nhimbong]

Hi,

I have a simple crosstab

Row:  Field "Productline" with 2 Product A and B
Columm: Field "booking year" with 2 values "2011" and "2012"
Measure: number of bookings
I want to have a comparision 2012 vs. 2011 in %
I can not add a query calculation, because the value for 2011 and 2012 belongs to the same dimension "booking year"

The result of a query will look like this
Bookings        2011     2012       Comparison
Produc A         100       70            -30%
Product B        100      150           +50%               

Another example for the field "booking date". I have the calculated field as "current_ date" and " current_date -7" (for the same weekday in prior week)

Bookings          current_date     current_date -7     Comparison
Product A             100                   70                      -30%
Product B             100                  150                    +50%

How can I add the field for the comparision? Could you please help?

Thanks a lot

[MFGF]

Hi,

Are you using a relational or dimensional package? The technique will vary between the two...

MF.

[nhimbong]

Hi,
It is a relational package.

So the calculation for this is (70 -100)/100 = -30%

If we have 2 different dimensions. It will be easy. But both values come from the same dimension!

I hope there will be a solution for that :)

Thanks
Nhimbong

[Satheesh]

Hi,
Create two data items
1. <2011> data item[size=78%]       [/size]
                if( <Booking year> = 2011) then (<measure>)
                else (0)

2. <2012> data item[/size]       [size=78%]
                if( <Booking year> = 2012) then (<measure>)
                else (0)
then you take One More Data Item/Query Calculation....write below code


(<2012>/<2011>)-1


Regards
Sateesh

[nhimbong]

Hello,

After being back from holiday I am very happy to see a suggession

It sounds very logic. The function works and there are also results from this calculation. But the result is not true and looks very funny by me

1.The value in data items are not as expected in the if-function
2.The value of "comparison" function ((<2012>/<2011>)-1)appears  in such many columns as the number of poductline  *2

I don't know what is wrong by me?

Thanks for your help

Nhimbong

[nhimbong]

Comparison-in-Crosstab.jpg

Here is the picture :-)