Data Studio
2.1K members online now
2.1K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello guys, 

 
We found some bugs with DataStudio and Postgres that make it hard to use. 
When you create formula (calculated field) DataStudio for big tables, we got the error "Invalid formula".
We assume that could be it's due to with the following problems:
1. Each time when you create formula (calculated field) DataStudio sends a query to the database to get sum of fields used in the formula.
 
For example when you create formula for cpc as spend/clicks it sends the query:
SELECT SUM(clicks) AS _clicks_, SUM(spend) AS _spend_ FROM adsets_raw_table_youtube AS t0 LIMIT 100; 
 
This query is non-optimal, it doesn't specify any WHERE conditions.
In cases when the table is not big the database returns result promptly and formula will be created. When the table is big the database responds longer (tens of seconds for example) and after some time (some predefined timeout) DataStudio returns "Invalid formula".
 
Screen for this error:
 

Снимок экрана 2017-04-20 в 22.58.14.png

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

@Kirill V

 

Your formula is not correct. 

Try this : 

Sum(spend/clicks)

 

It will be ok, i guess Smiley Happy

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

I have a similar issue to the OP. I tried your formula but that doesn't work either. I get the error message "reaggregating metrics not supported". 

 

I also tried to do the formula spend/conversions like the OP but that didn't work. My data is in google sheets. Ideas?

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

I am having the same issue as well. Any ideas? 

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Why isn't there any solution for this problem yet?

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

We have 2 calculated fields (eg.: "Revenue (sum(order_total)" and "month_days (count.distinct(days_in_range)") and we want forecast the entire month with another formula using both fields "Forecast=(revenue/month_days)*30".

 

If we build the formula using the calculated fields as they are, this would return a "Invalid formula".

 

The solution was replace the "month_days" formula for a constant, such as "1", then create the Forecast formula and then replace back the "month_days" to its original formula (count.distinct(days_range).

 

This worked for us.

Error "Invalid formula" for created calculated fields in DataStudio with connector PostgreSQL

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hey, 

I had the same problem and I think it does not have to do with your db size, but with the GDS postgres connector.
In the connection dialogue, instead of choosing a table, use a custom query:


select *
from schema_name.table_name

This enabled creating formulas for me.