[Angular - Pivot Table] Conditions in calculated field

[Emile Leenhardt]

Hello,

I would like to use a calculated field on the pivot table, but I can't manage to have it work.

My pivot has elements described as follow

1. type : "A" or "B"
2. days : Natural integers (>=0)

I want my calculated field to sum the quantities for my elements having type A.

What I found in the documentation (see link below) is following syntax 'condition ? then : else

So I tried to implement it as  "type"=="A"?"Sum(days)":0 but I seem unsuccessful.

Do you have any recommendation ?

Regards,

Emile

https://ej2.syncfusion.com/angular/documentation/pivotview/calculated-field#supported-operators-and-functions-for-the-calculated-field-formula 

[Karthickraja MuthuRaman]

Hi Emile,

Thank you for reaching out to the Syncfusion support.

We have validated your query and understand that you need to use the conditional operator in the calculated field formula. If that is the case, please note that the calculated field formula supports the conditional operator only for fields available within the calculated field and for conditions based on numerical values. It does not support string-based header values. However, since you need to use the conditional operator based on header information in the value field, we recommend using the aggregateCellInfo event to customize the value instead.

In the response below, we demonstrate how to use the conditional operator in a calculated field formula and how to achieve a value using the conditional operator based on header information.

Query	Response
conditional operator in a calculated field formula	In the code example below, we demonstrate how to use the conditional operator in a calculated field formula. When the 'Units Sold' field contains a value greater than 45,000, the formula returns 1; otherwise, it returns 0.   Code example: this.dataSourceSettings = {       values: [         { name: 'Sold', caption: 'Units Sold' },         { name: 'cField', caption: 'Calc Field' },       ],       …       calculatedFieldSettings: [         {           name: 'cField',           formula: '"Sum(Sold)" > 45000 ? 1 : 0'         }       ]  };   Output:   Meanwhile, we have prepared a sample for your reference, which you can access using the following link: Sample: https://stackblitz.com/edit/angular-na9j8zgz-zcfr2sim?file=src%2Fapp.component.ts
value using a conditional operator based on header information	To render a value using a conditional operator based on header information, you need to create a calculated field for the value and then utilize the aggregateCellInfo event to customize it. Within the aggregateCellInfo event, you can access the row and value axis information using the args parameter. By checking the axis and formattedText of the row, you can apply your custom logic. Please refer to the code example below:   Code Example: [app.component.html] <div class="control-section">     <ejs-pivotview … (aggregateCellInfo)='aggregateCellInfo($event)'>     </ejs-pivotview> </div>   [app.component.ts] aggregateCellInfo(args: any): void {     const row = args.row;     if (args.fieldName === "cField" && row.valueSort.axis === 'type') {         args.value = row.formattedText === "A" ? args.value : 0;     } } ngOnInit(): void {         this.dataSourceSettings = {         …         values: [{name: 'days'}, {name: 'cField'}],         ….         calculatedFieldSettings: [             {                 name: 'cField',                 formula: 'Sum(days)'             }         ]     }; }     Output:   Meanwhile, we have prepared a sample for your reference, which you can access using the following link: Sample: https://stackblitz.com/edit/angular-mj6xxwgv-kydxzm4r?file=src%2Fapp.component.ts

For more details on calculatedFieldSettings and the aggregateCellInfo event, please refer to the following documentation:

• Calculated Field Settings UG: https://ej2.syncfusion.com/angular/documentation/pivotview/calculated-field
• AggregateCellInfo Event UG: https://ej2.syncfusion.com/angular/documentation/api/pivotview/index-default#aggregatecellinfo

If you have any further questions or need additional assistance, please do not hesitate to contact us. We are here to help and ensure your concerns are addressed.

Regards,

Karthickraja