Calculate Difference

[zyus]

I hv table and sample data as below

Mth Prod Amt
Jan A 100
Jan B 150
Feb A 200
Feb B 100

When i do a report i've sorted & grouped based on 1) prod and 2)mth where i
create group header. My report will look like this

Prod A Jan 100
Feb 200

Prod B Jan 150
Feb 100

Q..How do i calculate variance for Feb & Jan by product and probably my
report will look like this


Prod A Jan 100
Feb 200
Var 100

Prod B Jan 150
Feb 100
Var -50

Appreciate your help.

 

[Stefan Hoffmann]

hi,

Q..How do i calculate variance for Feb & Jan by product and probably my
report will look like this

Create a grouped query and use the Var() aggregation, something like

SELECT
[Product],
[Month],
Var([Price])
FROM
[yourTable]
GROUP BY
[Product],
[Month]



mfG
--> stefan <--

 

[zyus]

I tried with this SQL but returned 0 for the variance

SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
FROM [Tbl-VAR]
GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;

My sample table (Tbl-VAR) are as follow :

Product Mth Amt
P Jan 100
K Jan 500
P Feb 250
K Feb 200


TQ

hi,

Q..How do i calculate variance for Feb & Jan by product and probably my
report will look like this

Create a grouped query and use the Var() aggregation, something like

SELECT
[Product],
[Month],
Var([Price])
FROM
[yourTable]
GROUP BY
[Product],
[Month]



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I tried with this SQL but returned 0 for the variance

SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
FROM [Tbl-VAR]
GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;

Product Mth Amt
P Jan 100
K Jan 500
P Feb 250
K Feb 200

The Variance can only be calculated if there is more than one value per
aggregation, e.g. with our data you can only the variance per year
(drop the group by month.



mfG
--> stefan <--

 

[John W. Vinson]

hi,

I tried with this SQL but returned 0 for the variance

SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
FROM [Tbl-VAR]
GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;

Product Mth Amt
P Jan 100
K Jan 500
P Feb 250
K Feb 200

The Variance can only be calculated if there is more than one value per
aggregation, e.g. with our data you can only the variance per year
(drop the group by month.



mfG
--> stefan <--

I think Zyus is using "variance" to just mean the change from month to month,
not the statistical measure.

John W. Vinson [MVP]