Crosstab - Calculation

[Valerie Wong]

Hi, I created a query of products with their values every quarter, as shown
below:

Product Qtr 1 Qtr 2 Qtr 3 ... Qtr n
1 5 6 5 5
2 1 2 3 4
3 10 10 10 10
4 5 6 2 3
i 5 6 7 4

I would like to calculate the quarterly change in value so for example, for
product 1, the quarterly change between Q1 and Q2 would be (6-5)/5=20% How
could I do this calculation either completely automatically or by a click of
command button? And also how could I select which quarterly change I would
like it to calculate?

Any help would be much appreciated.

Regards,
Valerie

 

[KARL DEWEY]

Post the SQL of your crosstab.

 

[Valerie Wong]

Hi Karl, please see below SQL

TRANSFORM Sum([TBL Portfolio Review].[GP Value as of Report Date]) AS
[SumOfGP Value as of Report Date]
SELECT [TBL Portfolio Review].[Fund ID], [TBL Portfolio Review].[Investment
ID]
FROM [TBL Portfolio Review]
GROUP BY [TBL Portfolio Review].[Fund ID], [TBL Portfolio
Review].[Investment ID]
PIVOT Format([Fund Report Date],"Short Date");

Many thanks.

Valerie

 

[KARL DEWEY]

Try this --
TRANSFORM Sum([TBL Portfolio Review].[GP Value as of Report Date]) AS
[SumOfGP Value as of Report Date]
SELECT [TBL Portfolio Review].[Fund ID], [TBL Portfolio Review].[Investment
ID], (Sum(IIf(Format([Fund Report Date],"yyyy q")="2008 4",[GP Value as of
Report Date],0))-Sum(IIf(Format([Fund Report Date],"yyyy q")="2008 3",[GP
Value as of Report Date],0))/Sum(IIf(Format([Fund Report Date],"yyyy
q")="2008 3",[GP Value as of Report Date],0))) AS [Quarterly change Q3 - Q4]
FROM [TBL Portfolio Review]
GROUP BY [TBL Portfolio Review].[Fund ID], [TBL Portfolio
Review].[Investment ID]
PIVOT Format([Fund Report Date],"yyyy q");

If it does not work for you say what is wrong and post your data input and
results.

Hi Karl, please see below SQL

TRANSFORM Sum([TBL Portfolio Review].[GP Value as of Report Date]) AS
[SumOfGP Value as of Report Date]
SELECT [TBL Portfolio Review].[Fund ID], [TBL Portfolio Review].[Investment
ID]
FROM [TBL Portfolio Review]
GROUP BY [TBL Portfolio Review].[Fund ID], [TBL Portfolio
Review].[Investment ID]
PIVOT Format([Fund Report Date],"Short Date");

Many thanks.

Valerie

Post the SQL of your crosstab.