Aviva Phillips schrieb:
We are having a problem creating a variance column. The report has the
following columns
Previous Yr Results Current Yr Results Variance Current
to Prior
The previous year column is the result of a query that sums the amtfieldq
where the select clause includes the statement where year = (cur year - 1).
The current year column uses the result of the same query that sums the
amtfieldq where the select clause includes the statement where year =
curyear.
The variance field is meant to calculate by subtracting previous yr results
from current yr results. The report is returning junk. We have tried
using a crosstab with control breaks and the command resultq -
previous(resultq) but that subtracts from the previous line, not the last
result on the same line. (We are using Sybase.)
If it is correct that you have 2 queries which return this data, you should be
able to do what you described. Take care of the dimensions involved, and, make
sure the measures are aggregated correctly. If not, use the formula: sum()-sum().
If you don’t have 2 measures, but only one object with a dimension
, with 2 possible values, you can use the formula
where (=“actyearnumber”) - where
(=“prevyearnumber”). In this case, however, you must know the previous and
actual year values literally.
A “dynamic approach” to this 2-period variance reporting is the following: include
in your query an indicator (or create it locally in the report) which
gives you, e.g. “A” if the in that row is the actual period and a “P” if
the period is the previous period. You can do this comparing the to a
user-entered “actual period” value). In case you don’t want the user to enter
anything in the query but always use e.g. the database system date as “actual
date”, you must have the indicator built into the universe.
Then create one single query which returns at least: , (and the
) for BOTH periods (actual + previous) and use the following formula to
compute the variancen the report:
= where (=“A”) - where (=“P”)
hope this helps.
Walter
–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria
Tel: +43-1-8151456-12, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Listserv Archives (BOB member since 2002-06-25)