Place "Count" conditions on Aggregate Models

[ktyler]

i have an aggregate model that counts the number of times a first name last name combination appears in the client object can i filter the model to only display > 1 for the count ?

[Zach_McElrath]

Yes, this is possible, but currently only through XML. Essentially what you are trying to accomplish is a “Post-Aggregation” Condition. Whereas a normal “Pre-Aggregation” Condition affects the set of records that get included in your aggregate query, a “Post-Aggregation” Condition is applied after the aggregate query is already run. Taking your example, say you have 1000’s of Client records. Say, for instance, you are only interested in Clients whose Age is over 65. You would write a “Pre-Aggregation” Condition (Conditions you’re used to with Skuid right now) to limit the scope of Client records that your Client_dups Aggregate Model even looks at. So, then, your Aggregate Model is run. It groups all Clients over the age of 65 by some field, then runs your aggregation — a Count. So now you have, say, 15 “aggregate result” records. Now, you want to apply a Condition to THESE 15 aggregate result records — finding just the ones where the Count is greater than 1. THIS is the scenario where “Post-Aggregation” Conditions come into play. Skuid currently only supports Post-Aggregation Conditions via XML, so for anyone reading this who’s uncomfortable working with XML — PLEASE, WAIT FOR THIS TO BE SUPPORTED IN THE PAGE BUILDER. But, for those of you at home in XML, here we go… In the Salesforce world, Post-Aggregation Conditions are achieved via a “HAVING” Clause. So in Skuid XML, we store Post-Aggregation Conditions in a node, with each Post-Aggregation Condition being defined as a separate . Other than that slight syntactical difference, having nodes are exactly the same as condition nodes, so basically if you just think of how your Skuid Condition XML would look, that’s how your Having XML should look. The only difference is that Havings / Post-Aggregation Conditions can only operate on Fields that you either Grouped or Aggregated on. Havings / Post-Aggregation Conditions are applied, by definition, only to the result set — so the only “fields” that are available to filter on are fields in your aggregate result set. So here’s the XML that you will need to add to your Client_dups Model XML to achieve what you’re after: Here’s a screenshot of a Model XML just to give some context: The key piece to notice here that may not be familiar is the “fieldfunction” XML attribute. This is where you select the aggregation function that you are filtering on. And here’s what it looks like on a working finished page — exactly the same, except that only rows where COUNT(Id) is 2 or more are included:

[Craig_Rosenbaum]

Is there anyway to leverage another aggregate field as the value parameter? I tried using that field’s alias and it did not work.

Also, any way to leverage these via a table filter for the user to click on/off? I tried assigning a name and it did not show up as a manual condition filter option, but anyway to do this in XML?

[Zach_McElrath]

Craig, not sure what you mean by your first question, can you elaborate?

On the second question, no, Filters do not currently support Havings/Post-Aggregation Conditions, but they will in our upcoming Skuid release. More than likely you still won’t be able to create Havings/Post-Aggregation Conditions from the Page Composer, but Post-Aggregation Conditions will be filterable via Skuid UI and via JavaScript.

[Craig_Rosenbaum]

In your example, value is hard coded to =1. I was asking if there is a way to make that dynamic by setting it equal to an aggregation from the model instead of 1. Does that make sense?

[Dave]

@_Deleted_User1

Yes, this is possible, but currently only through XML. Essentially what you are trying to accomplish is a “Post-Aggregation” Condition. Whereas a normal “Pre-Aggregation” Condition affects the set of records that get included in your aggregate query, a “Post-Aggregation” Condition is applied after the aggregate query is already run. Taking your example, say you have 1000’s of Client records. Say, for instance, you are only interested in Clients whose Age is over 65. You would write a “Pre-Aggregation” Condition (Conditions you’re used to with Skuid right now) to limit the scope of Client records that your Client_dups Aggregate Model even looks at. So, then, your Aggregate Model is run. It groups all Clients over the age of 65 by some field, then runs your aggregation — a Count. So now you have, say, 15 “aggregate result” records. Now, you want to apply a Condition to THESE 15 aggregate result records — finding just the ones where the Count is greater than 1. THIS is the scenario where “Post-Aggregation” Conditions come into play. Skuid currently only supports Post-Aggregation Conditions via XML, so for anyone reading this who’s uncomfortable working with XML — PLEASE, WAIT FOR THIS TO BE SUPPORTED IN THE PAGE BUILDER. But, for those of you at home in XML, here we go… In the Salesforce world, Post-Aggregation Conditions are achieved via a “HAVING” Clause. So in Skuid XML, we store Post-Aggregation Conditions in a node, with each Post-Aggregation Condition being defined as a separate . Other than that slight syntactical difference, having nodes are exactly the same as condition nodes, so basically if you just think of how your Skuid Condition XML would look, that’s how your Having XML should look. The only difference is that Havings / Post-Aggregation Conditions can only operate on Fields that you either Grouped or Aggregated on. Havings / Post-Aggregation Conditions are applied, by definition, only to the result set — so the only “fields” that are available to filter on are fields in your aggregate result set. So here’s the XML that you will need to add to your Client_dups Model XML to achieve what you’re after: Here’s a screenshot of a Model XML just to give some context: The key piece to notice here that may not be familiar is the “fieldfunction” XML attribute. This is where you select the aggregation function that you are filtering on. And here’s what it looks like on a working finished page — exactly the same, except that only rows where COUNT(Id) is 2 or more are included:

Hey Old post, but I guess the ‘Having’ method only applies to Salesforce Data? I just tried same exact logic on a DSO but it’s not working.

@skuid Since then any other method that this can be accoomplished?

[Rob_Hatch]

Yes Dave - this is only supported on the Salesforce Datasource. there are a number of Salesforce exclusive featuers for aggregations that are really nice that we get for free. We have to build them out specifically for other datasources, and have not done so yet. I believe you had previously discussed grouping by date literal functions. Thats the other big one I know about.

[hetalnathwani]

Hi @Zach_McElrath ,

Thank you so much the explanation.

I filtered the aggregate result field using having clause but it’s not getting any filtered result. Here is the screenshot of my XML model. Can you please give any example that how can we use HAVING clause in XML.

Thanks,
Hetal

[Mark_L]

After your <groupby> section, you can add your <havings> section. It looks like you’re doing that correctly. You may need to experiment with your syntax for your individual having. Here’s an example that works for me when I want to filter out zero values.
<havings> <having field="MyAmount__c" fieldfunction="SUM" operator="!=" value="0" enclosevalueinquotes="false"/> </havings>

in SOQL this would translate to

HAVING SUM(MyAmount__c) != 0

placed after the GROUP BY