Calculated column in analytic view - To get the value for power of a variable that is (Variable) to the power of (1/x)

[darshansomaiah1]

Hi Everyone,

In Analytic view, for the calculation of the formula CAGR https://en.wikipedia.org/wiki/Compound_annual_growth_rate,

Created a calculated column "CAGR", I have divided the Revenue of the current year by Revenue of 3 years ago and rounded the values as follows:

if ("REVENUE_3" = 0, 0, round((REVENUE_0"/"REVENUE_3"),0))

And, maintained with Data type: Integer, Column type: Measure

And I have not checked Calculate Before Aggregation

The values from the "CAGR" calculated column satisfies the calculation requirement.

After that I have created a new calculated column "CAGR_FIN" and given the formula as follows:

("CAGR")^(1/4)

And, maintained with Data type: Integer, Column type: Measure

And I have not checked Calculate Before Aggregation

The issue is the values of "CAGR_FIN" are giving as 1 for all the values (including zeroes) except for Null with respect to Customer (Attribute) drill down. And this is not satisfying the requirement.

I have checked the few threads, but could not get the details for "to the power of" operator.

Please suggest if I have missed any steps.

Regards,

Nachappa

[muthuram_shanmugavel2 · Accepted solution]

Hi Nachappa,

To calculate ("CAGR")^(1/4) in HANA Calculated column,

Please mention ("CAGR")^(0.25) in Formula Editor. You will get the correct results

For same, If you have to calculate for 5 years, You can mention that 

("CAGR")^(0.2) Instead of ("CAGR")^(1/5).

You will get the output you want

[darshansomaiah1]

Hi Thiru, Muthuram, Ganapathy,

After an analysis with few customers data with SALES_0 and SALES_3 (Excel), it is observed the suggestion provided before is working as per the requirement (Thanks all!) i.e., the operator ^ and **, but I should make use of decimal part of 1/x. Example: 0.25 of 1/4.

The error screenshot which I shared before is due to specific case only as shown in the screenshot.

And the details are as shown below:

When the SALES_3 is negative then the power of 0.25 is giving an error, as a basic mathematics issue that is root of any negative number is an imaginary. This was causing an error!!

So, I am thinking to restrict SALES_0 and SALES_3 greater than zero then perform the CAGR operation, else mark it as Zero.

Thanks Everyone.. It helped a lot!!

Regards,

Nachappa S

[Former Member]

For creating a Calculated Column that stores the power of a Measure we have to use the operator "**". For calculating the power to 1/n, you need to use the result of 1/n such as 0.5 for 1/2 or .25 for 1/4

So the expression is

CAGR_FIN**(.5)

If n in 1/n is stored in another column or calculated column then you can use

CAGR_FIN**(1/"COLUMN_NAME")

Attached a demo as well...

Make sure to give a DECIMAL  data type  (with the length and scale) and you would be able achieve your results.

PS: To add a note the expression editor accepts operators and functions that are listed in the bottom part and anything else will return an error.

[thirus11]

Hi,

The mathematical functions supported are given in the below link, Please check

Mathematical Functions - SAP HANA Modeling Guide for SAP HANA Web Workbench - SAP Library

Else you can try this way,

A power of a quotient (fraction) is equal to a quotient of powers of a dividend (numerator) and a divisor (denominator):

( a / b ) ⁿ =  a ⁿ /  b ⁿ

The fact that x to the power of n = b solves x = b to the power of 1/n.

Try refining your calculated column with the above math

Hope this would help you!!

Best regards,

Thiru

[darshansomaiah1]

Thiru,

Thanks for the reply.

But I could not decipher the statement "The fact that x to the power of n = b solves x = b to the power of 1/n"

Here I could not get how to use the "x"

As my request is ("CAGR") to the power of (1/4). Please correct me if I missed anything.

Regards,

Nachappa S

[thirus11]

Hi Nachappa,

SQRT: This function is used to return the square root of the argument n (which is supported)

CAGR to the power of 1/4 can be written as ((CAGR to the power of 1/2)to the power of 1/2) which is nothing but

sqrt(sqrt(CAGR)) = The value which you need

Hope this is correct!!

Best Regards,

Thiru

[darshansomaiah1]

Thiru,

Please find the steps followed on the pervious reply,

Created a calculated column as follows:

But, receiving an error when drilled down against Customer as shown,

Is this error due to data type issue with respect to sqrt function? Please suggest.

Regards,

Nachappa S