MODE Formula in Incorta

msamuels5 — Wed, 15 Mar 2023 00:11:08 GMT

I there a quick way to enact a Mode formula in Incorta to show the most common value within a measure array?

Re: MODE Formula in Incorta

JoeM — Wed, 15 Mar 2023 01:00:00 GMT

As you probably found, there isn't an aggregate method in Incorta that supports this. A few ways you could try are:
1) Leveraging insights over results or a SQL view to count occurrences of data and rank the results.

2) Leverage a MV and import the python statistics package

statistics.mode(measure)

Re: MODE Formula in Incorta

msamuels5 — Thu, 16 Mar 2023 19:46:17 GMT

Thanks Joe, yes I need this at an aggregate level. Unfortunately I don't construct MV's as well as not versed in Python.

Re: MODE Formula in Incorta

ZachBreimayer — Fri, 17 Mar 2023 22:47:07 GMT

Hi MSAMUEL,

Ill preface this by offering hands on assistance if needed.

To clarify the problem, essentially we want the value with the most occurrences within a field.

To expand upon Joes response you can derive this quite easily with SQL in an Incorta Materialized view, I want to illustrate this for you with the following example(s):

We have a table SalesOrderDetails which includes a field 'UnitPrice'

Now lets say we want to know what UnitPrice occurs most frequently, we want to count the number of records 'TheCount' and group by 'UnitPrice', we then can Order By the 'Count(*)' DESC to ensure the LineTotal with the most occurances is the first record in the result of the inner query. From here we can select 'TheCount' and 'LineTotal AS MODE' in the outer query and limit to just that first row with 'LIMIT 1'.

In the result shown below we can see that our mode is 4.99 with 671384 occurances.

RESULT

From here there are several options including but not limited to the following:

1: Join it back into the model

2: Apply the SQL logic and cross join into the source table(within the sql definition)

3: Define an internal session variable: This will make it globally usable across any formulas or elsewhere you want to simply reference the variable(mode) itself. I think this is possibly the best option especially if you want any level of detail without having to apply aggregate functions to persist the correct value(Avg) in downstream formulas.

Now we can go back to 'SCHEMAS' and create an internal session variable as shown here:

We can then hit the test button and see our mode:

Now in the formula builder you can just use the variable to get the mode and reference it in formula builder.

If we simply validate and save we can now see the Mode in the viz

We can then drag the fields from salesorderdetail in and we get the same result for each record:

This brings us to a small nuance, internal session variables will result in a string so in order to do a mathematical calculation you can simply wrap the '$MODE' with double as 'double($MODE)'

We can visualize the new field 'UnitPrice-Mode' and compare to the adjacent fields used in the calc:

If you need any help implementing a solution please reach out and I would be happy to assist.

Regards,

Zach, Solution Engineer - Incorta

topic MODE Formula in Incorta in Dashboards & Analytics Discussions

MODE Formula in Incorta

Re: MODE Formula in Incorta

Re: MODE Formula in Incorta

Re: MODE Formula in Incorta