Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  3. #3
    2 Star Lounger
    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 year-end 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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  5. #5
    2 Star Lounger
    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.

  6. #6
    2 Star Lounger
    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.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls in pivot table calculated fields (Excel 2003, XP)

    I'm afraid I can't help you with this, sorry.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  9. #9
    2 Star Lounger
    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 year-end 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 1-page 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.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  11. #11
    2 Star Lounger
    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 year-to-date correctly.

    Thank you both so much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •