>

>CLOSE TABLES ALL
>
>CREATE TABLE sampledata (party_name c(20), prod_name c(20), yearname N(4), sal_qty N(6), target_qty N(6))
>
>INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT A',2010,45)
>INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2010,40)
>INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT A',2011,30)
>INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2011,25)
>INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2011,25)
>
>INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT A',2010,50)
>INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT B',2010,45)
>
>INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT A',2011,40)
>INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT B',2011,45)
>USE
>
>LOCAL oexcel AS Excel.APPLICATION
>LOCAL oWorkbook AS Excel.WorkBook
>LOCAL oPivotCache AS Excel.PivotCache
>LOCAL oPivotTable AS Excel.PivotTable
>
>
>DATAPATH=SYS(5)+CURDIR()
>oexcel = CREATEOBJECT("excel.application")
>oexcel.APPLICATION.VISIBLE = .T.
>oWorkbook = oexcel.Workbooks.ADD()
>oTargetSheet = oWorkbook.Sheets.ADD()
>oTargetRange = oTargetSheet.RANGE("A2")
>oPivotCache = oWorkbook.PivotCaches.ADD( 2 ) && external data
>oPivotCache.CONNECTION = "OLEDB;Provider=vfpoledb.1;data source=" + DATAPATH
>oPivotCache.Commandtext = "select * from sampledata"
>oPivotTable = oPivotCache.CreatePivotTable( oTargetRange, "PivotTable" )
>oPivotTable.PivotFields("party_name").ORIENTATION = 1 && row
>oPivotTable.PivotFields("prod_name").ORIENTATION = 1 && row
>oPivotTable.PivotFields("yearname").ORIENTATION = 2 && column
>oPivotTable.PivotFields("sal_qty").ORIENTATION = 4 && data
>oPivotTable.PivotFields("target_qty").ORIENTATION = 4 && data
>
>* Calculate % change
>oChangeField=oPivotTable.AddDataField(oPivotTable.PivotFields("sal_qty"), "year_avg")
>oChangeField.Calculation= 3  && xlPercentOf
>oChangeField.BaseField="yearname"
>oChangeField.BaseItem="(previous)"
>
>oPivotTable.CalculatedFields.ADD("avg_diff", "=sal_qty/target_qty*100")
>oPivotTable.PivotFields("avg_diff").ORIENTATION = 4 && data
>
>* Calculate % change
>oChangeField=oPivotTable.AddDataField(oPivotTable.PivotFields("avg_diff"), "avg6")
>oChangeField.Calculation= 3  && xlPercentOf
>oChangeField.BaseField="yearname"
>oChangeField.BaseItem="(previous)"
>

>
>Showing Wrong Grand Total for "Avg_diff"
>
>warm regards,
>mk.


Hi,

According to your definition of avg_diff, it is CALCULATED field, having value as sal_qty/target_qty*100.
If you apply your definition, then in Grand Total it should be 165/180 * 100 = 91.6666666666667.
And it is what one can see in the pivot table.

So, what is wrong?