Skip to content
Advertisement

Bigquery error when powering negative number with decimal(float64)

I’m trying to calculate the CAGR using the formula in BigQuery

select
    power((last_audited_year_financial_reports.net_income / last_four_audited_year_financial_reports.net_income), 1/3) - 1 as three_year_cagr,
from ...

When running the query, somewhere in middle the values become POW(-0.0310044, 0.333333) and I’m getting error

Floating point error in function: POW(-0.0310044, 0.333333)

I tried calculating the same value in a calculator and I get the result. What could be the problem and how do I solve it?

Advertisement

Answer

Try Below Code:

select case when last_audited_year_financial_reports.net_income < 0 
then 
-1*power((-1*(last_audited_year_financial_reports.net_income) / last_four_audited_year_financial_reports.net_income), 1/3) - 1 

else 

power((last_audited_year_financial_reports.net_income / last_four_audited_year_financial_reports.net_income), 1/3) - 1 end as three_year_cagr,
from ...


Example for same error message:

SELECT case when -0.0310044 < 0 then -1* power(-1*(-0.0310044),0.333333) 
else power(0.0310044, 0.333333) end
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement