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