Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Mar 29, 2017 1:12 am

I tried to add a formula but I really need a calculated field.

I can manually add a calculated field to do what i want (pivotfield1/(pivotfield1 + pivotfield2)).

I went to add this to my pivot table by pt.CalculatedFields.Add('Name', "'pviotfield1'/('pivotfield1' + 'pivotfield2')"); but i get an error saying object reference not set to an object stacktrace: at Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable.8()

any ideas how to add a calculated field? There is hardly any documentation on pivot tables

ppdevs
 
Posts: 20
Joined: Sat Jan 09, 2016 7:23 pm

Wed Mar 29, 2017 7:13 am

Dear ppdevs,

Thanks for your inquiry.
Sorry that at present Spire.XLS doesn't support to add the calculated field, but we have added this new feature into our schedule. Once there is any progress, we will inform you soon. So sorry for inconvenience caused.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Mon Oct 22, 2018 5:38 pm

Hi,

I am using free Spire.XLS in my project to generate Pivotal Table.

I want to applied calculationfield on Pivotal table but it is not working.

ptAccuracybyRisk.DataFields.Add(ptAccuracybyRisk.PivotFields["HighCount"], "Total High Errors", SubtotalTypes.Sum);
ptAccuracybyRisk.DataFields.Add(ptAccuracybyRisk.PivotFields["LowCount"], "Total Low Errors", SubtotalTypes.Sum);
ptAccuracybyRisk.DataFields.Add(ptAccuracybyRisk.PivotFields["TotalCount"], "Total Processes", SubtotalTypes.Sum);

ptAccuracybyRisk.CalculatedFields.Add("Accuracy % by Risk", "= 1-(('HighCount'+'LowCount')/'TotalCount')");


Can you please help me how to do that.

Thanks
Subhankar Pradhan

pradhan.subhankar@gmail.com
 
Posts: 8
Joined: Fri Oct 12, 2018 12:42 am

Tue Oct 23, 2018 4:04 am

Hello Subhankar,

Thanks for your inquiry.
I did notice that there is a System.NullReferenceException when applying CalculatedFields with our Spire.XLS free version. Sorry that we have no plan to maintain the free version at present. Please upgrade to our commercial version (Spire.XLS Pack(Hotfix) Version:8.10.2) and remove "=" from your string formula like the following code snippet to test. If there is still any question after trying, please provide your input Excel file to help us further investigate your issue. You could send it to us via email (support@e-iceblue.com).
Code: Select all
......
ptAccuracybyRisk.CalculatedFields.Add("Accuracy % by Risk", "1-(('HighCount'+'LowCount')/'TotalCount')");
......

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Return to Spire.XLS

cron