Results 1 to 11 of 11

20080619, 16:09 #1
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Nulls in pivot table calculated fields (Excel 2003, XP)
Hi everybody:
Got a pivot table problem. I'm adding calculated fields to a pivot table, which work fine as long as none of the fields contain null values. (The fields are themselves calculated first). Excel doesn't seem to like the Nz function and is nulling out the results. Anybody know a workaround?
Thank you, in advance, for your help.

20080619, 16:19 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
Nz is an Access function, it is not available in Excel.
I'm not sure that I understand your problem. Unlike a field in Access, a cell in Excel cannot really be null  Excel treats blank cells, empty strings and zero values as more or less the same. Can you provide an example?

20080619, 16:35 #3
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
Hi Hans:
Thanks for your response.
It's not about cells being null; its about calculated fields in the pivot table code producing null values that I need to convert to 0s.
What I am doing is first creating calculated fields for monthly balances (using code below case 3: result is, i.e. for April: ' "=('4/30/2008 Balance' '4/1/2008 Balance' )/'4/1/2008 Balance'") and then using those fields to create calculated field for YTD
(also using code below, case 5). The problem seems to be that some of the funds don't have monthly balances for all the months to date and I need to zero these out so the YTD calculation will work instead of returning a null or empty string in my pivot table report.
Code to produce monthly returns fields:
__________________________
intNum = 0
For intNum = 0 To intMo  1 'loop backward through months in current year,starting with current month
dtWork = fnMonthEnd(dtMoEnd, intNum)
strField = fnPTFldName(dtWork, 3)
strCalc = fnPTCalc(dtWork, 3)
pt.CalculatedFields.Add strField, strCalc 'Must use fields in the query for pivot cache
pt.PivotFields(strField).Orientation = xlDataField
pt.PivotFields("Sum Of " & strField).NumberFormat = "0.00%"
pt.PivotFields(strField).NumberFormat = "0.00%"
Next intNum
_________________
code to produce calculation to go in the pivot field calc:
_________________
Function fnPTCalc(dtDate As Date, btType As Byte) As String
'Build string for calculation in pivot table field
'from date argument
Dim dtBOM As Date, strBOM As String, strCalc As String, strYear As String, strPYear As String
Dim strEOM As Date, dtEOM As Date, intMo As Integer, intQ As Integer, intCt As Integer, strWork As String
Dim strMonth As String, dtWork As Date
Const strErr As String = "fnCalc error: "
On Error GoTo err_fnPTCalc
intMo = Month(dtDate)
dtBOM = CDate(intMo & "/1/" & Year(dtDate))
dtEOM = fnMonthEnd(dtDate)
strBOM = fnStrDate(dtBOM)
strEOM = fnStrDate(dtEOM)
strYear = Year(dtDate)
Select Case btType
Case 3 'monthly percentage calc
strCalc = "=('" & strEOM & " Balance'  '" & strBOM & " Balance')/'" & strBOM & " Balance'"
' '3/31/2008 Balance' '3/1/2008 Balance' )/'3/1/2008 Balance'
Case 5 'YTD or yearend percentage calc
' i.e., "=((1+Jan_return_2008)*(1+Feb_return_2008)*(1+Mar_ return_2008)* (1+Apr_return_2008))1"
intCt = 0
For intCt = intMo  1 To intCt Step 1
dtWork = fnMonthEnd(dtDate, intCt)
strMonth = Format(dtWork, "mmm")
strWork = strWork & "(1+" & strMonth & "_return_" & strYear & ")*"
'strWork = strWork & "(1+Nz(" & strMonth & "_return_" & strYear & "),0)*" 'THIS DOESN'T WORK
Next intCt
strWork = Left(strWork, Len(strWork)  1) 'strip off last *
strCalc = "=(" & strWork & ")1"
Case Else
MsgBox strErr & "No calc available for " & btType & ". Please see Application Developer.", vbOKOnly
fnPTCalc = ""
GoTo exit_fnPTCalc
End Select
fnPTCalc = strCalc
exit_fnPTCalc:
Exit Function
err_fnPTCalc:
MsgBox strErr & Err.Description
Err.Clear
fnPTCalc = ""
Resume exit_fnPTCalc
End Function
So, for example, if a fund started in February and has no returns in January, its Jan_return_08 pivot table field returns a null, which nulls out my YTD calculation for that fund.
Hope this is clear.
Thanks!

20080619, 16:49 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
I don't ever use calculated fields in a pivot table, so I don't know much about them, but can you change the line
strCalc = "=('" & strEOM & " Balance'  '" & strBOM & " Balance')/'" & strBOM & " Balance'"
to
strCalc = "=IF(OR(ISBLANK('" & strEOM & " Balance'),ISBLANK('" & strBOM & " Balance')),0,('" & strEOM & " Balance'  '" & strBOM & " Balance')/'" & strBOM & " Balance')"
or is that not allowed in a pivot table? If it is allowed, does it help?

20080619, 16:55 #5
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
Thanks, I'll give it a try.

20080619, 17:33 #6
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
I tried your code for the monthly balance: it produced
=IF(OR(ISBLANK('6/30/2008 Balance'),ISBLANK('6/1/2008 Balance')),0,('6/30/2008 Balance'  '6/1/2008 Balance')/'6/1/2008 Balance')
I also tried code below for calculating the YTD, but my guesstimate as to how to use the IF and OR and ISBLANK functions doesn't work:
strWork = strWork & "(1+ IF(OR(ISBLANK(" & strMonth & "_return_" & strYear & "),ISBLANK(" & strMonth & "_return_" & strYear & "),0)" & ")"
produces:
=((1+Jan_return_2008)*(1+ IF(OR(ISBLANK(Jan_return_2008),ISBLANK(Jan_return_ 2008),0))(1+Feb_return_2008)*(1+ IF(OR(ISBLANK(Feb_return_2008),ISBLANK(Feb_return_ 2008),0))(1+Mar_return_2008)*(1+ IF(OR(ISBLANK(Mar_return_2008),ISBLANK(Mar_return_ 2008),0))(1+Apr_return_2008)*(1+ IF(OR(ISBLANK(Apr_return_2008),ISBLANK(Apr_return_ 2008),0))(1+May_return_2008)*(1+ IF(OR(ISBLANK(May_return_2008),ISBLANK(May_return_ 2008),0))(1+Jun_return_2008)*(1+ IF(OR(ISBLANK(Jun_return_2008),ISBLANK(Jun_return_ 2008),0))1
and when I try to add the YTD calculated field, Excel tells me it has run out of memory (run time error 7).
Sorry I can't attach the workbook: it is only two sheets, but even zipped up, it's bigger than 100k. My module is attached.
Thank you for all your help.

20080619, 18:00 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
I'm afraid I can't help you with this, sorry.

20080619, 18:34 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
Any chance you can post a simplified workbook showing the data layout you have, the current pivot table layout (copy and paste special>values to keep size down), and what you are trying to create  typed in manually if necessary to show the desired result?
Regards,
Rory
Microsoft MVP  Excel

20080619, 21:59 #9
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nulls in pivot table calculated fields (Excel 2003, XP)
Hi Rory:
Thanks for your help.
I already posted the text file with my module code, which runs from a command button on another sheet which I couldn't include due to size limitations.
The data comes in from an Access .mdb file and contains all the fields needed to calculate the monthly percentage returns and, from those, the YTD. None of the recordset values are nulls; I used Nz in the query to get zeros where there were blanks.
First, the PivotTable code creates calculated fields for the monthly returns, using the calculation: (end_mo_balance  beg_mo_balance) / beg_mo_balance for each monthly balance PivotTable field. Then, I create a calculated field for YTD using the monthly return PivotTable fields.
I use the fnPTCalc code below to derive strings similar to, i.e. for YTD as of April: ' "=((1+Jan_return_2008)*(1+Feb_return_2008)*(1+Mar_ return_2008)* (1+Apr_return_2008))1"
which is the calculation we use. My code returns the correct string, and it works in cases where all the months have returns, but not the others.
Calculation snippet where intMo is the current month number and dtDate is the current date. fnMonthEnd returns a date which is the last day of the month argument.
Case 5 'YTD or yearend percentage calc
intCt = 0
For intCt = intMo  1 To intCt Step 1
dtWork = fnMonthEnd(dtDate, intCt)
strMonth = Format(dtWork, "mmm")
strWork = strWork & "(1+" & strMonth & "_return_" & strYear & ")*"
Next intCt
strWork = Left(strWork, Len(strWork)  1) 'strip off last *
strCalc = "=(" & strWork & ")1"
Attached is a 1page Excel file with the report result. If you look in the first few records (all Convertible Arbitrage), you will see that YTD numbers are missing, even though some of them have monthly balances in 2008. My theory was that the reason the calculated YTD pivot table fields are nulling out is that the monthly return pivot fields are coming up null in cases where a fund had no balances in that month, but perhaps the reason is that there are divide by 0 errors when the beginning month balance was zero. I've checked the data, and it seems to bear this out.
So, I think what we need to do is to figure out how to use the IF OR ISNULL functions to ensure that a 0 beginning month balance field used in the monthly performance calc is somehow disarmed. The PT should use the calculation unless the beginning balance is zero, in which case it should return zero.
Thank you for any light you can shed on this.

20080620, 00:44 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Nulls in pivot table calculated fields (Excel
If you use Hans' suggestion for the monthly figures, do you get zero values for the previously null data? If so, then your YTD function ought to be able to just use those calculated fields as they are.
Regards,
Rory
Microsoft MVP  Excel

20080620, 15:26 #11
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nulls in pivot table calculated fields (Excel
No, I tried that. Hans's string in the calculation of the monthly return pivot field still resulted in empty cells rather than zeros.
I tried to work around the problem by changing my database query to return zeros instead of nulls, but had the same problem.
I have finally solved it, using: strCalc = "=IF('" & strBOM & " Balance' = 0,0,('" & strEOM & " Balance'  '" & strBOM & " Balance')/'" & strBOM & " Balance')"
This results in zeros in monthly return fields where the beginning of month balance is zero and calculates the quarters and yeartodate correctly.
Thank you both so much for your help!