on 02-10-2016 9:31 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ) n = a n / b n
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.