custom formula

posted on April 23rd, 2020

Answered

mp asked on April 23, 2020

Hello,
I would need to compute the distinctcount of a field "in percentage", so that, for each cell, the computed value would be equal to distinctcount('Field')/TOTAL, where TOTAL equals the grand total of distinctcount('Field') (grand total for both rows and columns).
Is there any way I can achieve this?
Thank you in advance.

6 answers

Public

Mykhailo Halaida ⋅ Flexmonster ⋅ April 24, 2020

Hi Marta,

Thank you for writing to us.

Flexmonster does offer a built-in percent aggregation function which retrieves a percentage of the field's grand total. However, currently, this function only calculates the values based on the field's sum.

Currently, it is not possible to calculate the percent of distinct count total, neither with a specific aggregation nor with a calculated value. As an alternative, a potential solution would involve solving this problem on the server-side with your own custom data source API implementation, although this way requires more coding.

Please let us know if you have any other questions we can help you with.

Best regards,
Mykhailo

Public

mp ⋅ April 28, 2020

Hello,
Thank you for the answer. Following your suggestion, I am trying to do it with the custom data source API, but I need some more help because something is not working.
This is what I've tried:

I've downloaded and installed the custom data source API for Github (more precisely, the node js version of the API).
I've added a new aggregation to the API. For that, I've modified the cube.js file in the following way:
- getFields function: I've added a new aggregation called "percentcount" to the output constant that is returned as fieldsCache by the function,
- calcValue function: I've added a new if condition so that, when func equals "percentcount" it returns the distinctcount value, which is then divided by the total in getSelectResult funciton
- getSelectResult function: after computing the distinctcount, I divide every value into the total

However, this approach is not working. The first problem is that, in the "select" POST request, the "percentcount" aggregation is not being requested. I think this is because the javascript library does some pre-process in the measures, and it does not recognise the new aggregation name.
I've attached a working example so that you can test it. I've tried using a measure defined both by a formula and with an aggregation. In the first case, I get the error "Wrong formula format"; in the second case, it requests for the "sum" instead of "percentcount" (I think because it may be the default option).
Thank you again for the assistance.
Best regards

Attachments:
flexmonsterAPI.rar

Public

Mykhailo Halaida ⋅ Flexmonster ⋅ April 30, 2020

Hi Marta,

Thank you for your detailed response and our apologies for the slightly delayed response.

Please note that the names of the aggregations defined in your implementation of the custom data source API have to be among the ones listed here: https://www.flexmonster.com/doc/support-more-aggregations/

With that in mind, we've tested the modified cube.js file you've provided us with and all the aggregations seem to be loaded properly if we change the percentcount name to percent, for example – percent is one of the supported aggregation names in this case.

This will result in your aggregation being displayed under the name % of Grand Total, which is the default caption for the percent aggregation. In case you would like to modify its caption, this can be achieved through localization of the aggregation:

global: {
    localization: {
        "aggregations": {
            "percent": {
                "caption": "% of Total Distinct Count",
                "totalCaption": "% of Total Distinct Count of {0}",
                "grandTotalCaption": "Total % of Total Distinct Count {0}"
            }
        }
    }
}

More info on the localization approach can be found here: https://www.flexmonster.com/doc/localizing-component/

We hope you find this helpful.

Best regards,
Mykhailo

Public

Mykhailo Halaida ⋅ Flexmonster ⋅ May 7, 2020

Hi Marta

How are you?

We were wondering if our last response helped you with your issue. Was it resolved, or are there any further questions you would like to discuss?

Please let us know your thoughts.

Regards,
Mykhailo

Public

mp ⋅ May 7, 2020

Hello,
Sorry for the delay. I've tried your suggestions and it worked perfectly. Thank you very much for your help!
However, as a suggestion, I think that it would be very nice to have a way to allow Flexmonster to recognise any aggreagtion name when using the custom datasource API.
Best regards

Public

Mykhailo Halaida ⋅ Flexmonster ⋅ May 8, 2020

Hi Marta,

Thank you for the heads up, it is good to hear everything is working fine for you!

Speaking of your request, yes, it is already in our plans to allow defining a custom aggregation under a caption of your choosing. We will make sure to make an announcement when this becomes available.

As always, do not hesitate to reach out if you have any other questions we can help you with.

Best regards,
Mykhailo

Changes to Flexmonster Software License Agreement

custom formula

6 answers

Please login or Register to Submit Answer