Solved

Excel Pivot Table - Totals Aren't Working

Posted on 2011-03-25
13
280 Views
Last Modified: 2012-06-27
Attached is an Excel file with a macro that creates a Pivot table and the other file contains the Pivot table itself.  I was given this on a previous question, but didn't notice that the totals don't work.  After trying some things manually, it said I couldn't change this part of the pivot table.  I can make it work if I turn on the macro recorder and create the pivot table from scratch, but then it wouldn't include the start and end times like I want.  Thank you.
logonEvents.csv
LogonReport.xls
LogonReportStarter2.xls
0
Comment
Question by:rvfowler2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35229959
Could you clarify what exactly is wrong with the pivot table as it is in the workbook you provided?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232464
The totals are wrong.  They don't add up to the sum of the daily hours the person put in for the week.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232525
That's just a formatting issue - you need to format the Hours field to display as [hh]:mm rather than hh:mm
HTH
Rory
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35232539
In other words, change this line:
.PivotFields("Total Hours").NumberFormat = "hh:mm"

Open in new window

to this:
.PivotFields("Total Hours").NumberFormat = "[hh]:mm"

Open in new window

0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35232613
Thanks, that worked.  Missed an easy one.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232682
Actually, it fixed that issue, but if you do it, I have tons of other issues.  Whoever helped me before totally messed up what I had that was working.  All I asked for in a previous question was to add a start and ending time.  I think I'm going to manually record the process and start over.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232911
If you are referring to this PAQ: http:/Q_26831553.html then that was me. Since you gave no feedback or explanation of a problem then, or now, not much I can do about it. (I don't really see how formatting the output causes you a problem - the values are still the same).
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232997
Just make the change as you indicated top [hh], then run the report.  I've included the data file that I cleaned up a bit this a.m.  I'm still looking through it, but wondering if your code simply calcs the elapsed time between beginning and end times rather than add up the elapsed times because sometimes people log in and out a couple of times per day.

 Copy-of-logonEvents.csv
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233175
The code adds up the same formulas you were using originally. Can you give me an example where it is wrong? as far as I can see, the formulas total to 606:57 hours of work, as does the pivot table.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233371
Yes, sorry, for the most part, it seems that I need to so some data cleanup.  It would be nice if the code could account for orphaned logon and logoffs.  I did run the old code which I am copying below, and it came out just a bit better than your code in producing totals closer to accurate (compare Randy to Randy), but there were still problems.  See attached report as the result of the old code.
Sub FormatLogonReport_OLDER()
'
    ChDir "R:\LogonScript"
    Workbooks.Open Filename:="R:\LogonScript\Copy of logonEvents.csv"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="R:\LogonScript\LogonReport.xls", FileFormat _
        :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    Application.DisplayAlerts = True

'ENTER HEADERS
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("1:1").Select
    Selection.Font.Bold = True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Userid"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Workstation"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Day"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Event"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Hours"
  
    Cells.Select
    Cells.EntireColumn.AutoFit

    Columns("A:G").Select
    Range("G1").Activate
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

'FORMAT COLUMN AND ENTER HOURS CALC
    Columns("G:G").Select
    Selection.NumberFormat = "h:mm;@"
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "f").End(xlUp).Row
    Dim rng As Range, cell As Range
    Set rng = Range("F2:F" & lrow)
    For Each cell In rng
        If UCase(cell.Value) = "LOGOFF" And UCase(cell.Offset(-1, 0)) = "LOGON" _
            Then cell.Offset(0, 1).Formula = "=(d" & cell.Row & "+E" & cell.Row & ")-(d" & cell.Row - 1 & "+E" & cell.Row - 1 & ")"
    Next cell
    
    'INSERT THE PIVOT TABLE
    Windows("LogonReport.xls").Activate
    Cells.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Copy of logonEvents'!$A:$G").CreatePivotTable TableDestination:= _
        "'[LogonReport.xls]Copy of logonEvents'!C9:C14", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Userid", _
        "Day")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hours")
        .Orientation = xlDataField
        .Caption = "Sum of Hrs Worked"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Userid")
        .PivotItems("administrator").Visible = False
        '.PivotItems("temp").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day")
        .PivotItems("(blank)").Visible = False
    End With
' FINAL FORMATTTING
    Columns("K:K").Select
    Selection.NumberFormat = "[h]:mm"
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    Columns("I:I").Select
    Selection.Font.Bold = True
    Columns("I:K").EntireColumn.AutoFit
    Range("J2:K2").Select
    Selection.Font.Bold = True
    ActiveWorkbook.Save
    
    'Windows("LogonReportStarter.xls").Activate
    'ActiveWindow.Close
    
End Sub

Open in new window

LogonReport2.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233437
Small point, but the only difference there is in the sorting, which is your code, not mine - I only added the columns to the pivot table. :)
What do you want to do with orphaned items? Ignore them? If so, you could add a check to see if the Day (or date) is the same as the row above in addition to checking for LOGON followed by LOGOFF?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233696
Yes, I think it's best to ignore the orphaned items.  If you only added the start and end times columns, not sure why the totals of the two reports would be different in some places.  As I mentioned you could compare Randy with Randy on the two reports.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question