Calculate Variance In Crosstab

  • Thread starter Thread starter zyus
  • Start date Start date
Z

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
 
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];
 
Thanks duane....its worked

Duane Hookom said:
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


zyus said:
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
Click to expand...
Click to expand...
 
Back
Top