Crosstab comparison

building reports webintelligence
1

I have a crosstab report where I am trying to compare the totals from yearly qtrs to the previous year’s qtrs. I’ve attached an excel file showing what I’m trying to achieve…

can anyone help me with this total comparison…

Thx
Sales.xls (28.0 KB)

squash junkie :netherlands: (BOB member since 2008-09-08)

2

PS: your attached file seems to be corrupted.

anyway try to make a variable that will hold the previous value of the previous quarter


PreviousValue = Previous([Quarter])

then just subtract it to your current quarter


Variance = ([Quarter] - [PreviousValue])

Hope that helps :smiley:

jvmauricio :philippines: (BOB member since 2009-08-03)

3

Yep, I’d tried the previous function but that doesn’t return the correct values, or gives me a “MULTIVALUE” error.
The crosstab has
Rows : Part A and Part B, with a total.
Columns : year and Quarter for the last 3 years
There is a break inserted between the years so the crosstab looks like its been split into threee seperate parts.
What I am trying to do is have a row in the bottom of each part where total Year 1 QTR 1 is compared to total Year 2 Qtr 1.
(I’ve attached the spreadsheet again, although it wasn’t corrupt when I d/l it.)

Of course I can create another table and do this comparison, but it would be nice to have on the same table.
Sales v2.xls (28.0 KB)

squash junkie :netherlands: (BOB member since 2008-09-08)

4

I’ve gotten the value of the previous quarter by doing the following:

Adding the quarter and year to the report. Sorting the report 1st by quarter, and then by year. Then I used this formula:

=Previous(Sum([Actual Meeting Expense Amount]);([Quarter of Meeting Date]))

I realize that the results aren’t sorted the way that you want them, but I did get the proper prior year/same quarter actual expense this way.

To use a cross-tab, I placed the quarter on the right side, the categories in the top, and the year underneath. The formula I used in the cross-tab is:

=Previous(Sum([Actual Meeting Expense Amount]);([Quarter of Meeting Date];[Meeting Type]))

Actual Meeting Expense Amount is the measure, and Meeting Type is a dimension in my universe (similar to the A, B values showing in your example). The easiest way for me to create the cross-tab was to create a flat vertical table first with the sorts I described, create the variable for the variance, add it in an extra column to the right, and then transform to a cross-tab.

bkaporch :us: (BOB member since 2008-12-02)