Solved: Pivot Tables from External Databases - Average formula incorrect

[malik641]

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.

[CBrine]

Malik,
Sounds to me like you may be getting a Null in one of the return values? In access, I would surround the offending field with NZ() to eliminate it. I'm not sure if that will work in SQL, but if the Datediff works, then it might as well. Can't test without the data.

nz(DateDIFF(d,Table1.Date1,Table1.Date2))

Maybe?
Cal

[malik641]

Cal,

NZ(variable, return_value_if_null) function doesn't work (it is not a found function), but IsNull(variable, return_value_if_null) did work, but the number still turned out to be the same.

I found the problem. After looking at the data in MS Query, I noticed that the item that was 10 days late was inputted twice in the database, with exactly the same information. So technically, the data would be:

Item ----- Days Late
Item1 ----- 10
Item1 ----- 10
Item2 ----- 0
Item3 ----- 0

Making the average correctly 5.00.

It's funny because when you use the Average of Days Late in the Data area of the Pivot Table, it shows Item1 once, and the average of it ((10+10)/2)=10 when there is supposed to be unique Items in the database. Just a silly mistake I guess (from whoever entered the data twice).

Thanks anyway Cal!

[CBrine]

Malik,
The surprise would have been if I actually was able to help you with anything that you weren't able to figure out on your own.

:-)

[malik641]

That wouldn't be a surprise to me