PVR101
asked on
pivot table
Hi – appreciate some help as continue to learn about pivot tables and their uses. Attached is a sample workbook to illustrate current problem.
How can I calculate the exceedance value for a column of data (% Grand Total from pivot table6) so for a given threshold limit value of say 2m the exceedance above this value is automatically computed (and associated plot updated) when I refresh the pivot table i.e red highlighted cells dynamically update with pivot table.
Is there any way I can amend the sheet/workbook so able to input a specific limit value eg 2.5m and determine the % value of exceedance above this value for all recorded data points? My attempt to do this is just by manually selecting computed results that are over the specified threshold limit but this is not a true accurate account of the results and cumbersome – see also on tables sheet countifs calc which I believe more accurately calculates exceedance above a specified value. Can this calculation and or above issue be incorporated into the source table so able to include in pivot table somehow?
Lastly and minor point but why is there a slight variance in the computed grand total % values in pivottable6 and 7 eg 22.90% vs 22.66%. It appears this is maybe due to fact one table shows values as % of column total and other % of grand total but unclear how arrived at.
ee-sample.xlsx
How can I calculate the exceedance value for a column of data (% Grand Total from pivot table6) so for a given threshold limit value of say 2m the exceedance above this value is automatically computed (and associated plot updated) when I refresh the pivot table i.e red highlighted cells dynamically update with pivot table.
Is there any way I can amend the sheet/workbook so able to input a specific limit value eg 2.5m and determine the % value of exceedance above this value for all recorded data points? My attempt to do this is just by manually selecting computed results that are over the specified threshold limit but this is not a true accurate account of the results and cumbersome – see also on tables sheet countifs calc which I believe more accurately calculates exceedance above a specified value. Can this calculation and or above issue be incorporated into the source table so able to include in pivot table somehow?
Lastly and minor point but why is there a slight variance in the computed grand total % values in pivottable6 and 7 eg 22.90% vs 22.66%. It appears this is maybe due to fact one table shows values as % of column total and other % of grand total but unclear how arrived at.
ee-sample.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
byundt - many thanks for reply.
In the tables sheet I note that cells T34 and U34 return #value errors causing the chart to plot incorrectly. also T39, T40 and U40 cells are blank ?
When I change the input cell T23 to 2m and refresh the table updates but does not resize to correct row number and so unfortunately chart plots incorrectly. The TL values revert back to 2-3 rather than 2-2.5 etc bins and although filters selected not sure why table does not list 2-2.5 and 2.5-3 row data? The TL column T31-T38 appears to jump from 1,2,4,5,6,7,8,9 missing 3 data out?
In the tables sheet I note that cells T34 and U34 return #value errors causing the chart to plot incorrectly. also T39, T40 and U40 cells are blank ?
When I change the input cell T23 to 2m and refresh the table updates but does not resize to correct row number and so unfortunately chart plots incorrectly. The TL values revert back to 2-3 rather than 2-2.5 etc bins and although filters selected not sure why table does not list 2-2.5 and 2.5-3 row data? The TL column T31-T38 appears to jump from 1,2,4,5,6,7,8,9 missing 3 data out?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent and thanks for refresh macro - had a quick play with sheet.
Am i right in saying this macro must be selected manually after each time change height TL cell T23 eg if change to 2.2 m or 3.1m etc - really handy if auto did this function.
For 2m TL cells s40 and u40 seem to be populated and not cut off at pivot table row bin lables? - is it possible to also auto sort the table so the bin labels are in order or need be manual. Oddly the shaded TL plot, plots from bin 1-2 rather than 2-3 so skewed??.
Lastly on full workbook i have a similar pivot table with exceedance for WindSpeed - how easy might it be to modify the macro to replicate wave height functions were WindSpeed is PivotTable8 and WaveHeight is pivottable14.
Am i right in saying this macro must be selected manually after each time change height TL cell T23 eg if change to 2.2 m or 3.1m etc - really handy if auto did this function.
For 2m TL cells s40 and u40 seem to be populated and not cut off at pivot table row bin lables? - is it possible to also auto sort the table so the bin labels are in order or need be manual. Oddly the shaded TL plot, plots from bin 1-2 rather than 2-3 so skewed??.
Lastly on full workbook i have a similar pivot table with exceedance for WindSpeed - how easy might it be to modify the macro to replicate wave height functions were WindSpeed is PivotTable8 and WaveHeight is pivottable14.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Byundt,
I have tweaked my full workbook for windspeed and wave height using your code so when trigger cells changed the PTs auto update. This works to a fashion - my problem is that the PTs do not auto sort ascending and the attached slicers default to no filters selected so I need to manually select all relevant data filters and sort PTs ascending. How can the code be amended to correct this.?
Sub RefreshWaveHeightPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("HeightBins").Value
For i = 1 To 2 'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable13", "PivotTable14", "PivotTable15", "PivotTable20") 'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Height Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name,
pi.Visible = Not IsError(v)
End Select
Next
pt.RefreshTable
pt.PivotFields("Height Group").AutoSort xlAscending, "Height Group"
Next
Next
Set pt = .PivotTables("PivotTable14
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).Ent
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub
and
Sub RefreshWindSpeedPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("SpeedBins").Value
For i = 1 To 2 'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable7", "PivotTable8", "PivotTable2", "PivotTable21", "PivotTable12", "PivotTable17", "PivotTable24") 'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Speed Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name,
pi.Visible = Not IsError(v)
End Select
Next
pt.RefreshTable
pt.PivotFields("Speed Group").AutoSort xlAscending, "Speed Group"
Next
Next
Set pt = .PivotTables("PivotTable8"
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).Ent
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub
Cell x35 on windspeed PT appears blank and should probably indicate >=50 –does formula need to be modified for this? Similar issue on cell x235 for wave height although note data extends below row labels for some reason?
Wondering if there is a better way to plot the exceedance (red shaded area) plots as currently skewed and not vertical from defined TL limit eg 2m height TL exceedance plots from <1.0m bin rather than from 2-3 bin, similarly speed plots from 15-20 bin rather than 25-30 bin given a defined 25knts TL .
I have PMd you full workbook so able to see above and tweaks made.
I have tweaked my full workbook for windspeed and wave height using your code so when trigger cells changed the PTs auto update. This works to a fashion - my problem is that the PTs do not auto sort ascending and the attached slicers default to no filters selected so I need to manually select all relevant data filters and sort PTs ascending. How can the code be amended to correct this.?
Sub RefreshWaveHeightPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("HeightBins").Value
For i = 1 To 2 'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable13", "PivotTable14", "PivotTable15", "PivotTable20") 'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Height Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name,
pi.Visible = Not IsError(v)
End Select
Next
pt.RefreshTable
pt.PivotFields("Height Group").AutoSort xlAscending, "Height Group"
Next
Next
Set pt = .PivotTables("PivotTable14
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).Ent
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub
and
Sub RefreshWindSpeedPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("SpeedBins").Value
For i = 1 To 2 'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable7", "PivotTable8", "PivotTable2", "PivotTable21", "PivotTable12", "PivotTable17", "PivotTable24") 'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Speed Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name,
pi.Visible = Not IsError(v)
End Select
Next
pt.RefreshTable
pt.PivotFields("Speed Group").AutoSort xlAscending, "Speed Group"
Next
Next
Set pt = .PivotTables("PivotTable8"
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).Ent
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub
Cell x35 on windspeed PT appears blank and should probably indicate >=50 –does formula need to be modified for this? Similar issue on cell x235 for wave height although note data extends below row labels for some reason?
Wondering if there is a better way to plot the exceedance (red shaded area) plots as currently skewed and not vertical from defined TL limit eg 2m height TL exceedance plots from <1.0m bin rather than from 2-3 bin, similarly speed plots from 15-20 bin rather than 25-30 bin given a defined 25knts TL .
I have PMd you full workbook so able to see above and tweaks made.
ASKER
Discovered HeightBins and SpeedBins ranges were not pointing to correct cells in my workbook! - corrected to point to
=Index!$I$5:$I$16 and =Index!$C$18:$C$28 respectively on full workbook but still issues with sorting groups?
Is it just PT 14 that the sort will work on or all height PTs eg 13,14,15,20. similarly for speed PTs 7,8,2,12,17?
=Index!$I$5:$I$16 and =Index!$C$18:$C$28 respectively on full workbook but still issues with sorting groups?
Is it just PT 14 that the sort will work on or all height PTs eg 13,14,15,20. similarly for speed PTs 7,8,2,12,17?
The macros are expecting named ranges SpeedBins and HeightBins, against which they check whether to display bins in the PivotTables. These named ranges don't exist in your workbook, and must be added. They should point to Index!$I$5:$I$16 and Index!$C$18:$C$28.
I tried testing the code, but your production file locks up my Excel. I use Excel 2013 on Windows 7 on my 2008 Mac laptop, running everything on a virtual machine using Parallels. It is possible that my virtual machine doesn't have enough memory to run your production file, though it was able to run the code in the smaller workbook posted in the question.
The sort is supposed to work on all the PivotTables you have listed in the Array statement in the macro.
I tried testing the code, but your production file locks up my Excel. I use Excel 2013 on Windows 7 on my 2008 Mac laptop, running everything on a virtual machine using Parallels. It is possible that my virtual machine doesn't have enough memory to run your production file, though it was able to run the code in the smaller workbook posted in the question.
The sort is supposed to work on all the PivotTables you have listed in the Array statement in the macro.
ASKER
Hi byundt, I have included name ranges and pointed to index etc but when change TL and code does refresh PTs do not update/sort - must be missing something.
I am running full workbook on win7 pc with excel 2010 and 4GB ram. I have PMd you a reduced workbook so hopefully able to check and troubleshoot my error? can you think of a way to get the shaded plot to go vertical and not sloped when charting exceedance??
I am running full workbook on win7 pc with excel 2010 and 4GB ram. I have PMd you a reduced workbook so hopefully able to check and troubleshoot my error? can you think of a way to get the shaded plot to go vertical and not sloped when charting exceedance??
When you post a workbook, whether it be in the question thread or in a PM, Experts Exchange limits you to 40 characters for the filename. Making matters worse, they silently truncate the rightmost part of the filename if it exceeds that limit. This means that you lose part or all of the file extension, and never receive any warning that this was done.
I mention the above because your file names in the PM already exceed the limit. I guessed that you were posting a .xlsm file and was able to open it, but experts who hadn't stumbled across this "feature" before will be stymied.
I mention the above because your file names in the PM already exceed the limit. I guessed that you were posting a .xlsm file and was able to open it, but experts who hadn't stumbled across this "feature" before will be stymied.
ASKER
OK, good to know - thanks for the heads up. Hopefully received ok and yes is a xlsm file.
One approach for getting a sharp rise on the area chart is to return an error value instead of an empty string in the values being plotted. For example, change the formula (copied down) in Pivot Tables worksheet cell Y25 to:
=IF(X25<=$Y$19,NA(),W25)
The failure to sort is because the bin labels are treated as text, so "5 - 10" sorts after "40 - 45" and ">45" sorts before "0 - 10". One way to overcome this problem is to format the bin label values with two digits and eliminate ">". For example, "<5", "05 - 10", "10 - 20", "20 - 30", "30 plus". I made these changes to the two named ranges on the Index worksheet.
=IF(X25<=$Y$19,NA(),W25)
The failure to sort is because the bin labels are treated as text, so "5 - 10" sorts after "40 - 45" and ">45" sorts before "0 - 10". One way to overcome this problem is to format the bin label values with two digits and eliminate ">". For example, "<5", "05 - 10", "10 - 20", "20 - 30", "30 plus". I made these changes to the two named ranges on the Index worksheet.
ASKER
The good news is that the new code in Y25 etc works great and sorts the shaded plot. Also saved file as xlsb and halved size - excellent tip.
The bad news is that after a number of attempts to test the macro for sorting lables excel goes through the motions (code appears to work but tables do not update, spinning circle indicating work in progress finishes/disappears) then excel freezes where unable to move cursor etc - no error messages appear - task manager shows as 'running' and processor is around 00-01 so not doing anything in background. Forced to quit excel each time to resolve. It seems therefore somehow refresh code for sorting crashes excel even on reduced data size .
Just noted that the source of data TABLE1 is pointing to 'ee-PT100715.xlsm'!Table1 and not 'BUOY Data'!$A$1:$R$27182 in workbook and reduced data set row size. I changed the source of data link for PTs (7,8,2) (after removing clicer connections for all PTs) and re ran - Pleased to say code appears to fully work so looks like issue was the source data link was not updated after reduced file data size etc.
Why are x25 and y25 showing #value errors and x35 is blank?
Is there anyway to update the source data table for all PTs at once or just have to select each in turn and update?
The bad news is that after a number of attempts to test the macro for sorting lables excel goes through the motions (code appears to work but tables do not update, spinning circle indicating work in progress finishes/disappears) then excel freezes where unable to move cursor etc - no error messages appear - task manager shows as 'running' and processor is around 00-01 so not doing anything in background. Forced to quit excel each time to resolve. It seems therefore somehow refresh code for sorting crashes excel even on reduced data size .
Just noted that the source of data TABLE1 is pointing to 'ee-PT100715.xlsm'!Table1 and not 'BUOY Data'!$A$1:$R$27182 in workbook and reduced data set row size. I changed the source of data link for PTs (7,8,2) (after removing clicer connections for all PTs) and re ran - Pleased to say code appears to fully work so looks like issue was the source data link was not updated after reduced file data size etc.
Why are x25 and y25 showing #value errors and x35 is blank?
Is there anyway to update the source data table for all PTs at once or just have to select each in turn and update?
You might consider putting a 0 in cell X25. That will eliminate the error caused by the parsing formula. Cell Y25 should then return #N/A error value (needed so chart shows squared up left edge for exceedance.
X35 will be blank if the parsing formula doesn't find a hyphen in cell E35. I suggest just typing in what you want to display in cell X35 instead of using the formula.
I had previously faced the problem of the source data being on a different workbook, and added the following statement to the macros to address it:
X35 will be blank if the parsing formula doesn't find a hyphen in cell E35. I suggest just typing in what you want to display in cell X35 instead of using the formula.
I had previously faced the problem of the source data being on a different workbook, and added the following statement to the macros to address it:
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"ASKER
Thanks - where does the SourceData code go - in PT sheet or module1 / module 2 or both ? of workbook and where within the code should it be placed?
Any thoughts on why grand total values in for example PT8 do not update/refresh when change Y19?
Any thoughts on why grand total values in for example PT8 do not update/refresh when change Y19?
If you look, the SourceData statement has always been there. It is already in the macros on both Module1 and Module2.
ASKER
Sorry, missed that.
But why then do i need reset sourcedata for PTs if this as i understand it auto updates PTs for Table1 range and why are the PTs data values not apparently updating even though the bin lables now sort correctly eg if change TL from 25 to 30 data values remain same
But why then do i need reset sourcedata for PTs if this as i understand it auto updates PTs for Table1 range and why are the PTs data values not apparently updating even though the bin lables now sort correctly eg if change TL from 25 to 30 data values remain same
ASKER
similar refresh issue with the height TL and its data values if change relevant TL.
When testing for Height threshold limit of 2.5m TL I noted that the index bin assigned for this value is 03-03 which is a bit confusing/misleading - not sure if possible to tweak text formula to correctly show bin 2.5-3 as before if possible ??
When testing for Height threshold limit of 2.5m TL I noted that the index bin assigned for this value is 03-03 which is a bit confusing/misleading - not sure if possible to tweak text formula to correctly show bin 2.5-3 as before if possible ??
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have applied above changes to my reduced workbook and tested. To help troubleshoot I have further reduced data to only 1000 rows and re created Table1 range so hoepfully you will be able to test run your end. I disconnected all slicer connections. For all the PTs I manually (one by one) pointed all to the new Table1 range (some were defaulting to original full workbook range of 84734 and so not updating data values even though data values did not exist below 1000 rows) and updated all PT SPEED/HEIGHT/PERIOD filters to include new height and speed bins (most fliters were initially unchecked). When first updated (changed y19 to 20) the row lables did not sort correctly so manually corrected and saved file. I then re changed y19 to 25 - macro ran and again the labels did not sort correctly. see file.
Testing the height TL I ran at 2.5m from 2m and PT DID sort correctly - re ran at 2m and table again auto sorted correctly including exceedance plot and values etc.
So seems height sort works but speed sort does not??
Testing the height TL I ran at 2.5m from 2m and PT DID sort correctly - re ran at 2m and table again auto sorted correctly including exceedance plot and values etc.
So seems height sort works but speed sort does not??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent - should have known simple explanation. i did spot that PT 24 was deleted but did not realize implications if left in code - another useful lesson learnt.
Will test on full workbook and let you know how goes.
Thank you
Will test on full workbook and let you know how goes.
Thank you
ASKER