malik641
02-05-2007, 12:13 PM
Hey guys,
I have created a pivot table using the database at my job as an external reference (SQL Server 2000 database). I edited the query in MS Query to add fields that (to my knowledge) cannot be obtained otherwise. The specific field I'm having a problem with used the DateDIFF function in MS Query to display the amount of days between two dates. The formula itself works fine in the SQL statement, what I'm having a problem with is the Average formula that the Pivot table uses in the Data Items section of the pivot table of those days.
I've provided a workbook that is an example of what my pivot table kinda looks like (using the worksheet's data). The difference is the 'Days Late' column is the field where I used the DateDIFF formula in my actual workbook.
Here's a simplified version of what the SQL statement looks like:
SELECT Tabel1.Item1, Table2.Item2, DateDIFF(d,Table1.Date1,Table1.Date2) AS 'Days Late'
FROM MyDataBase.dbo.Table1 Table1, MyDataBase.dbo.Table2 Table2
WHERE Table1.ItemID = Table2.ItemID AND (Table1.Date2>='1/1/2007' And Table1.Date2<='12/31/2007')
The data returned that I'm concerned with is this:
Days Late
10
0
0
The workbook I provided shows a correct use of the Average function (3.33 being the average), although my actual workbook says the average is 5.00...what gives?
If I need to be more clear please say so. This is pretty new for me (pivot tables using external databases...and pivot tables too), so I'm not sure if I left out important information.
Many thanks in advance,
Joseph
EDIT: Sorry, forgot the workbook. Here it is.
I have created a pivot table using the database at my job as an external reference (SQL Server 2000 database). I edited the query in MS Query to add fields that (to my knowledge) cannot be obtained otherwise. The specific field I'm having a problem with used the DateDIFF function in MS Query to display the amount of days between two dates. The formula itself works fine in the SQL statement, what I'm having a problem with is the Average formula that the Pivot table uses in the Data Items section of the pivot table of those days.
I've provided a workbook that is an example of what my pivot table kinda looks like (using the worksheet's data). The difference is the 'Days Late' column is the field where I used the DateDIFF formula in my actual workbook.
Here's a simplified version of what the SQL statement looks like:
SELECT Tabel1.Item1, Table2.Item2, DateDIFF(d,Table1.Date1,Table1.Date2) AS 'Days Late'
FROM MyDataBase.dbo.Table1 Table1, MyDataBase.dbo.Table2 Table2
WHERE Table1.ItemID = Table2.ItemID AND (Table1.Date2>='1/1/2007' And Table1.Date2<='12/31/2007')
The data returned that I'm concerned with is this:
Days Late
10
0
0
The workbook I provided shows a correct use of the Average function (3.33 being the average), although my actual workbook says the average is 5.00...what gives?
If I need to be more clear please say so. This is pretty new for me (pivot tables using external databases...and pivot tables too), so I'm not sure if I left out important information.
Many thanks in advance,
Joseph
EDIT: Sorry, forgot the workbook. Here it is.