PxPlus User Forum
Main Board => Discussions => Programming => Topic started by: Thomas Bock on November 30, 2023, 02:57:10 AM
-
Has someone created a Pivot table with an average column so far?
I recorded a macro in MS-Excel
With ActiveSheet.PivotTables("Pivot1").PivotFields("Percent")
.Caption = "Average %"
.Function = xlAverage
End With
This is my translation to PxPlus
pivotName$ = "Pivot1"
fieldName$ = "Percent"
title$ = "Average %"
piTable = sheet'pivotTables(pivotName$)
piField = piTable'pivotFields(fieldName$)
piTable'addDataField(*piField)
lastItem = piTable'dataPivotField'pivotItems()'count
lastPiField = piTable'dataPivotField'pivotItems(lastItem)
lastPiField'caption$ = title$
piField'function = -4106 ! xlAverage
I always receive an error 88 on the last line.
The macro seems to be wrong, because the caption can only be set on lastPiField instead of piField.
Help is appreceated.
-
SOLVED!
The trick is to use piTable'dataFields()
lastItem = piTable'dataPivotField'pivotItems()'count
lastPiField = piTable'dataFields(lastItem)
lastPiField'function = -4106
lastPiField'numberFormat$ = mask$
lastPiField'caption$ = title$
-
Glad you resolved your issue.
Thanks for sharing the answer.