MODE Formula in Incorta

[msamuels5]

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

[JoeM]

@msamuels5 - 

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)

[msamuels5]

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

[ZachBreimayer]

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