Computing correlation in LookML as a Custom field

[diego_perea-166]

I am trying to implement correlation between two fields in Looker. It is indeed possible as a Table calculation, but I need to implement it as a custom field in LookML.

I tried the following:

#----------- test correlation:

measure: sum_test_1{
type: number
sql: ( SELECT ${TABLE}.{% parameter kpi %} + ${TABLE}."KPI_1" FROM ${TABLE} )
;;
}

measure: corr_test{
type: number
sql: (SELECT CORR( ${TABLE}.{% parameter kpi %}, ${TABLE}."KPI_1") FROM ${TABLE})
;;
}

With the outcomes:
1- sum_test_1. It works, it verifies that the two columns have valid data before compute correlation
2- corr_test. It produces the following error: 
"The PostgreSQL 9.5+ database encountered an error while running this query.

ERROR: Don't understand function: corr"

(See generated SQL at the bottom)

According to these results, it seems that  correlation is NOT supported by Looker as a custom field in LookML.
Can anybody confirm this? is there another method I should try?
--------------------------------------------------------------------------------------

SELECT
    (DATE(ERO_Agnostic."1d")) AS "ERO_Agnostic.Time_Dimension_1d",
    ERO_Agnostic.VOLTE_AFR  AS "ERO_Agnostic.kpi_name",
    0.5 *  ERO_Agnostic.VOLTE_AFR
       AS "ERO_Agnostic.counter_corr_1",
    ( SELECT ERO_Agnostic.VOLTE_AFR + ERO_Agnostic."DL_THPT"  FROM ERO_Agnostic )
       AS "ERO_Agnostic.counter_corr_3",
    AVG(ERO_Agnostic."DL_THPT") AS "ERO_Agnostic.DL_THPT",
    (SELECT CORR( ERO_Agnostic.VOLTE_AFR, ERO_Agnostic."DL_THPT") FROM ERO_Agnostic)
       AS "ERO_Agnostic.counter_corr_2"
FROM "ERO Agnostic"."ERO Agnostic" AS ERO_Agnostic
WHERE (ERO_Agnostic."Coverage_SiteID") = 'ON1344' AND ((( ERO_Agnostic."1d" ) >= (DATE_TRUNC('day', (DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-6 || ' day')::INTERVAL))) AND ( ERO_Agnostic."1d" ) < (DATE_TRUNC('day', ((DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-6 || ' day')::INTERVAL) + (7 || ' day')::INTERVAL)))))
GROUP BY
    1
ORDER BY
    1 DESC

[GavinW]

If you run the generated code directly in your database, what happens? 

[diego_perea-166]

The generated code with some changes (table and field names) runs fine in Google BigQuery.

[GavinW]

Interesting - but the error says it is running against PostgreSQL 9.5?

[diego_perea-166]

Yes, we are connecting to  tables in BQ but they are is layer in the middle that uses Postgress, maybe that is the problem?
Do you have experience of correlation working in other scenarios?

[GavinW]

I don't have any specific experience with using a correlation function, but it definitely looks like the problem is because the code that is being written will not work in Postgres - I wonder if it would work if you try tailoring the code to Postgres rather than BQ?