Get Free Trial

Custom Aggregations for custom DataSource API

Answered
Rodrigo Arantes asked on March 23, 2021

Hi,
  
How can I add custom aggregations for specific fields? I only can get to work custom aggregations when I add to the aggregations.type, like this:

aggregations: {
any: [“count”, “distinctcount”],
number: [“sum”, “count”, “distinctcount”, “average”, “min”, “max”, “custom1”, “custom2”],
date: [“count”, “distinctcount”, “min”, “max”],
},

But I actually need to add that at the field level and unfortunately this is not working for me. I’m referring to the aggregations property for a field, see below.

"fields"[]: {
        "uniqueName": string,
        "type": "string" | "number" | "date",
        "caption": string,
"hierarchy": string,
"parent": string,
        "folder": string,
        "interval": string,
       "aggregations"[]: string,
        "filters": boolean | {
            "members": boolean,
            "query": boolean | string[],
            "valueQuery": boolean | string[]
        }
    },

I’m using custom DataSource API, flexmonster for react version 2.8.28.

What is the proper way of adding custom aggregation functions? I need to add at least 4 (different weighted averages) and more to come.

9 answers

Public
Vera Didenko Vera Didenko Flexmonster March 24, 2021

Hello, Rodrigo,
 
Thank you for your question.
 
Custom aggregations can be added in the following way:

  1. First, specify the custom aggregations in the list of all available aggregations (in the aggregations property of the /fields response). Please see the following guide for reference.

    For example: 

    // The /fields response

    {
    aggregations: {
    any: ["count", "distinctcount"]
    date: ["count", "distinctcount", "min", "max"]
    number: ["sum", "count", "distinctcount", "average", "min", "max", "custom1"]
    },
    fields: [...],
    filters: {...}
    }
  2. Once the custom aggregation names are added to the list of all aggregations, it is possible to add them for specific fields. 
    By specifying the available aggregations for the fields, you could limit the aggregation list to contain only the needed aggregations. This way, you could specify which fields each custom aggregation should be available for.

    For example: 

    // The /fields response

    {
    aggregations: {
    any: ["count", "distinctcount"]
    date: ["count", "distinctcount", "min", "max"]
    number: ["sum", "count", "distinctcount", "average", "min", "max", "custom1"]
    },

    fields: [
    {
    aggregations: ["sum", "count", "distinctcount", "average", "min", "max", "custom1"]
    caption: "Price"
    type: "number"
    uniqueName: "Price"
    },
    {
    aggregations: ["sum", "count", "distinctcount", "average", "min", "max"]
    caption: "Quantity"
    type: "number"
    uniqueName: "Quantity"
    }
    ...
    ],

    filters: {...}
    }

        As a result, the "custom1" aggregation will be available for "Price" but not for "Quantity".
 
Please let us know if this helps and if everything works fine for you.
Looking forward to your response.
 
Kind regards,
Vera

Public
Rodrigo Arantes March 24, 2021

Hi Vera.

I thought that this object 

aggregations: {
any: ["count", "distinctcount"]
date: ["count", "distinctcount", "min", "max"]
number: ["sum", "count", "distinctcount", "average", "min", "max", "custom1"]
},

would behave as the default list inherited by all fields, and if I would want to override for a specific field(s) I would just need to declare that in the give field’s aggregations property.

I see it now.

Thanks Vera. That definitely helps

Public
Vera Didenko Vera Didenko Flexmonster March 25, 2021

Hello, Rodrigo,
 
Thank you for your response. 
 
We are happy to hear that you found our explanation helpful.
The aggregations property of the /fields response does behave as the default list inherited by all fields. However, it also acts as a definition of all aggregations that are supported on your server.
At the same time, our team understands that such an approach could not be convenient for cases when there are multiple fields.
With this in mind, we have added a task to our backlog, and our team will see how specifying custom aggregations for specific fields could be improved. We will keep you updated. 
 
Please feel free to reach out if further questions arise.
 
Kind regards,
Vera

Public
Vera Didenko Vera Didenko Flexmonster April 20, 2021

Hello, Rodrigo,
 
We are happy to announce that our team improved the flow for adding custom aggregations.
Now it’s possible to add a custom aggregation for a specific field without having to first specify it in the "aggregations" property of the /fields response.
 
This is available in the latest version of Flexmonster.
You are welcome to update the component. Here is our updating to the latest version guide for assistance.
 
Please let us know if the update works fine for you.
We would be grateful for your feedback.
 
Kind regards,
Vera

Public
Rodrigo Arantes April 29, 2021

Thanks for the update, Vera!

Let me ask you other questions related to custom aggregations.

I have a custom aggregation for a field of type “date” where the result of the aggregation is a number (number of years). It gets properly displayed on the pivot table, but when I export to excel
it shows as “date” (gets converted to date).
Is there a way to set the data type for custom aggregation?

And, is there a way to set user friendly names for custom aggregation?
I mean, “distinctcount” becomes “Distinct Count”. Is it possible to have it displayed that same way(CamelCase)?
Today, If I set up a custom aggregation as “Weighted Average Custom” it becomes “weighted average custom”.

Thanks
 
 

Public
Vera Didenko Vera Didenko Flexmonster April 30, 2021

Hello, Rodrigo,
 
Thank you for your response. 
 
About date aggregation values getting exported to Excel as dates instead of numbers:
Thank you for reporting this issue. We would like to confirm that this is not the expected behavior. Our team will look into this issue, and we will share the results with the ETA 14th of June.
As for names for custom aggregations, currently, they should be specified in lower case letters. 
 
A possible workaround is to create a calculated measure based on the field’s custom aggregation and use it in the slice.
For example: 

{
"uniqueName": "Year count",
"formula": "numberofyears(\"date\")",
"caption": "Year count"
}

Calculated measures are of type number, hence, in the exported Excel, the values will be displayed as numbers as well.
Such an approach will also provide more flexibility for custom aggregation names – you can specify any desired caption for calculated measures.
 
Please let us know if this would work for you and if any questions arise.
 
Kind regards,
Vera

Public
Vera Didenko Vera Didenko Flexmonster June 14, 2021

Hello, Rodrigo,
 
We are happy to inform you that the issue with formatting date/time values in Excel for number aggregations like count was fixed.
This is available in the latest version of Flexmonster (v2.9.1).
 
You are welcome to update the component. Here is our updating to the latest version tutorial for guidance.
 
Please let us know if the fix works fine for you.
 
Kind regards,
Vera

Public
Rodrigo Arantes June 18, 2021

Hi Vera
That worked! ?
Thanks for sending the update.

Public
Vera Didenko Vera Didenko Flexmonster June 22, 2021

Hello, Rodrigo,
 
Thank you for your feedback.
We are happy to hear that everything works.
 
Should further questions arise, feel free to reach out.
 
Kind regards,
Vera

Please login or Register to Submit Answer