CODEWITHSUNDEEP
sqlgoogle-bigquery
sumit-sampang-rai
sumit-sampang-rai

Reputation: 691

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?

Upvotes: 0

Views: 873

Answers (1)

Mohammad
Mohammad

Reputation: 678

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

Upvotes: 1

Related Questions