Cross Tab with Differences and Percent Differences 1

[elehmann]

Thanks for helping!
Using CR9.
We have a cross-tab with flight bookings summarized by Class-of-Service. We have detail records with bookings for last year and this year. Formula fields for ly(last year) and ty(this year) will have the number of bookings on the detailed record or 0 depending on whether the record is last year's - or this year's. We have another formula field that computes the difference for each row - and this is summarized by the cross-tab (this is always correct). We also have a % diff formula - which we average - and while we usually get the correct answer - sometimes we don't.

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


Number of Flight Bookings:


[Last Year][This Year] [Diff] [%Diff]
Class-of-Service xxx yyy yyy-xxx xx %

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

[Last Year] and [This Year] bookings are formulas summed by the crosstab

[Diff] = [This Year] - [Last Year] summed by the crosstab

[%Diff] = (({@diff}/{@lyrb}) * 100) AVERAGED by the crosstab

The problem is we get sometimes errors with the percent diff (most of the time it's correct)

here are some examples (of the errors)

Last yr This yr Diff %Diff (expected %diff)
49 3 -46 -83% -94%
1,536 72 -1,463 -88% -95%


How can compute the correct % differences?

Thank you!!!

 

[lbass]

I would try laying out your fields in the detail section so you can see how the diff% is being calculated. From your description, it sounds like you would get something like the following:

LastYr ThisYr Diff Diff%
8 0 -8 -100%
7 0 -7 -100%
5 0 -5 -100%
15 0 -15 -100%
14 0 -14 -100%
0 3 3 0%

49 3 -46 83% //Ave = 500%/6

So what you really need is a formula:

Sum({@diff},{table.svcclass}) % sum({@lastyr},{table.svcclass})

...which would give you -46/49 = -94%

I don't have CR 9.0, so am not sure of whether there is the capacity to use formulas with sums in crosstabs, but I'm guessing you might have to create a manual crosstab to do this.

-LB

 

[elehmann]

Thank you for your help!!! You were right, we can't put a summary formula into the cross-tab. We are looking at alternatives, including the manual cross-tab. Thanks, again.