Calculate Sum in Pagelist
Hi -
I am trying to calculate the sum of the property in a page list based on a condition. For example, I have 5 rows in a table, but only two of those rows meet the required condition. I would like to only calculate the sum of a property for those two rows. How can this be accomplished?
Thank you!
Accepted Solution
Updated: 9 Feb 2022 16:17 EST
Pegasystems Inc.
AU
This can also be done declaratively using a Declare Expression so that the result is always synchronised with your source data, using a concept called the Clipboard Property Collection which is a syntax that gathers a scalar property value from every member of a page list.
Using the Product List from the UI Gallery as an example, by having a Page List property on my case auto-populated from the results of a Data Page ...
... I can have a separate Property with a Declare Expression that performs an Aggregate Function (in our case, "Sum of") over Prices (pyPrice) from the .AllProducts() page list using the Clipboard Property Collection syntax: .AllProducts().pyPrice. Use of Aggregate Functions also optionally captures an Expression - or a When rule - to apply a filter on which members of the Page List are included in the aggregation. It looks like this:
In addition to Sum, there are also aggregation options for Count, Average, Minimum and Maximum.
This can also be done declaratively using a Declare Expression so that the result is always synchronised with your source data, using a concept called the Clipboard Property Collection which is a syntax that gathers a scalar property value from every member of a page list.
Using the Product List from the UI Gallery as an example, by having a Page List property on my case auto-populated from the results of a Data Page ...
... I can have a separate Property with a Declare Expression that performs an Aggregate Function (in our case, "Sum of") over Prices (pyPrice) from the .AllProducts() page list using the Clipboard Property Collection syntax: .AllProducts().pyPrice. Use of Aggregate Functions also optionally captures an Expression - or a When rule - to apply a filter on which members of the Page List are included in the aggregation. It looks like this:
In addition to Sum, there are also aggregation options for Count, Average, Minimum and Maximum.
The When rule must be resolvable from the Applies To class of the Page List property. In this case, Data-UIGallery-ProductInfo. In our case it is a simple expression, but this could be much more complex depending on the application need:
So even though there are 25 results returned in the ProductInfo data page, only 4 are of type Hardcover:
... and despite the total sum of prices across the result set being much bigger, the Declare Expression correctly narrows this aggregation down to 15 + 15 + 9 + 9 = 48
Declare Expressions are preferable for this so that you don't need to attach rules (e.g. Data Transforms) to UI Action Sets or Flow Action pre/post processing every time the calculation needs to be re-performed. Changes to your Page List data will trigger recalculation of the Declare Expression either as the source data changes, or the next time the Declare Expression property is referenced.
Updated: 24 Aug 2020 6:04 EDT
Commonwealth Bank of Australia
IN
Try this way, copy the rows of the pagelist which are in accordance with the condition to other pagelist using a Data Transform and then define a declare expression on the second page list.
I'll let you know if I get my hands on other methods.
Capgemini
NO
Hi Samuel,
We can achieve this through datatransform rule. Here is the sample DT where the Sum value will be stored in Param.Sum. You can call this DT based on your requirement and Map the Param.Sum.
Make sure to declare Sum in the parameter tab as Integer, Double or Decimal based on requirement.
Let me know if you have any questions while implementing the above.
Good Luck!
Accepted Solution
Updated: 9 Feb 2022 16:17 EST
Pegasystems Inc.
AU
This can also be done declaratively using a Declare Expression so that the result is always synchronised with your source data, using a concept called the Clipboard Property Collection which is a syntax that gathers a scalar property value from every member of a page list.
Using the Product List from the UI Gallery as an example, by having a Page List property on my case auto-populated from the results of a Data Page ...
... I can have a separate Property with a Declare Expression that performs an Aggregate Function (in our case, "Sum of") over Prices (pyPrice) from the .AllProducts() page list using the Clipboard Property Collection syntax: .AllProducts().pyPrice. Use of Aggregate Functions also optionally captures an Expression - or a When rule - to apply a filter on which members of the Page List are included in the aggregation. It looks like this:
In addition to Sum, there are also aggregation options for Count, Average, Minimum and Maximum.
This can also be done declaratively using a Declare Expression so that the result is always synchronised with your source data, using a concept called the Clipboard Property Collection which is a syntax that gathers a scalar property value from every member of a page list.
Using the Product List from the UI Gallery as an example, by having a Page List property on my case auto-populated from the results of a Data Page ...
... I can have a separate Property with a Declare Expression that performs an Aggregate Function (in our case, "Sum of") over Prices (pyPrice) from the .AllProducts() page list using the Clipboard Property Collection syntax: .AllProducts().pyPrice. Use of Aggregate Functions also optionally captures an Expression - or a When rule - to apply a filter on which members of the Page List are included in the aggregation. It looks like this:
In addition to Sum, there are also aggregation options for Count, Average, Minimum and Maximum.
The When rule must be resolvable from the Applies To class of the Page List property. In this case, Data-UIGallery-ProductInfo. In our case it is a simple expression, but this could be much more complex depending on the application need:
So even though there are 25 results returned in the ProductInfo data page, only 4 are of type Hardcover:
... and despite the total sum of prices across the result set being much bigger, the Declare Expression correctly narrows this aggregation down to 15 + 15 + 9 + 9 = 48
Declare Expressions are preferable for this so that you don't need to attach rules (e.g. Data Transforms) to UI Action Sets or Flow Action pre/post processing every time the calculation needs to be re-performed. Changes to your Page List data will trigger recalculation of the Declare Expression either as the source data changes, or the next time the Declare Expression property is referenced.