Calculate Variance In Crosstab

[zyus]

With my sql below

TRANSFORM Sum(QunionALLDATA.[NET_BAL]) AS SumOfNET_BAL
SELECT QunionALLDATA.[SUBCATDESC2]
FROM QunionALLDATA
GROUP BY QunionALLDATA.[SUBCATDESC2]
PIVOT QunionALLDATA.[MthTag];

it produced this result

subcatdesc2 current month previous month
A 1000 900
B 2000 3000

is it possible to calculate variance between current month & previous month
and i expect the new result will be as follow :


subcatdesc2 current month previous month var
A 1000 900 100
B 2000 3000 -1000


Thanks

 

[Duane Hookom]

Try:

SELECT [SUBCATDESC2], Sum(Abs(MthTag="Current Month") * Net_Bal) as
CurrentMonth,
Sum(Abs(MthTag="Previous Month") * Net_Bal) as PreviousMonth ,
Sum(Abs(MthTag="Current Month") * Net_Bal)-Sum(Abs(MthTag="Previous Month")
* Net_Bal) As Var
FROM QunionALLDATA
GROUP BY QunionALLDATA.[SUBCATDESC2];

 

[zyus]

Thanks duane....its worked

Try:

SELECT [SUBCATDESC2], Sum(Abs(MthTag="Current Month") * Net_Bal) as
CurrentMonth,
Sum(Abs(MthTag="Previous Month") * Net_Bal) as PreviousMonth ,
Sum(Abs(MthTag="Current Month") * Net_Bal)-Sum(Abs(MthTag="Previous Month")
* Net_Bal) As Var
FROM QunionALLDATA
GROUP BY QunionALLDATA.[SUBCATDESC2];

--
Duane Hookom
MS Access MVP

With my sql below

TRANSFORM Sum(QunionALLDATA.[NET_BAL]) AS SumOfNET_BAL
SELECT QunionALLDATA.[SUBCATDESC2]
FROM QunionALLDATA
GROUP BY QunionALLDATA.[SUBCATDESC2]
PIVOT QunionALLDATA.[MthTag];

it produced this result

subcatdesc2 current month previous month
A 1000 900
B 2000 3000

is it possible to calculate variance between current month & previous
month
and i expect the new result will be as follow :


subcatdesc2 current month previous month var
A 1000 900 100
B 2000 3000 -1000


Thanks