Use power function in beast mode or in the formula while creating ETL

user026529 · 2022-03-09T18:54:20+00:00

There was an error rendering this rich post.

I have 2 columns - revenue for yr 1, and revenue for Yr 5. I want to create a beast mode function to calculate the compounded Annual growth rate (CAGR) in beast mode.

I am trying to do this approach but its not working

Power (( 'rev Yr 5' / 'rev Yr 1'), 0.2) -1 but this formula is not working in Power function. I assume the reason is because power function expects a floating point integer value and we cannot pass a division calculation inside a power function?

in excel if I try this the logic is simple- (( 'rev Yr 5' / 'rev Yr 1')^ (1/5)) -1

PS: I am using 0.2 for power function because i am trying to calculate the CAGR for 5 yr hence 1/5 = 0.2

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

Hmm.... I just tried doing the same thing in the formula ETL tile and it worked. Here is what mine looks like using some sample data:

POWER((`DaysPast` / `Lag`),.2)-1

Do your rev Yr 5 and rev Yr 1 fields have nulls? It won't be able calculate unless they both have values. Is your formula validating in the editor, but then isn't producing results when you run the preview?

user026529

No the revenue columns dont have null values

see the screenshot.. its not validating itself

MarkSnodgrass

In your screenshot, it looks as though you are missing an open parentheses ( at the beginning as you should have two open parentheses together. You currently have one open parentheses and two closed ones.

user026529

ahh.. yes so when I correct the syntax it works.

One more Question -Is it possible to do this in beast mode? rather than ETL?

Because I see that beast mode has a function for Sum and ETL formula box doesnt support Sum

So I want to do something like this

POWER((Sum(`DaysPast`)) / Sum(`Lag`)),.2)-1

Basically sum these 2 columns before dividing them for the CAGR formula

MarkSnodgrass

Yes, that will work in a card beast mode. Just keep a close eye on the parentheses. You had one too many in what you just sent.

user026529

It doesnt compute when I do the same in Beast mode

MarkSnodgrass

It looks like you have a parentheses issue again. I see two open parantheses after your first sum. There should only be one there. You also have one too many, just before the comma. Try removing those and see if it works. Sometimes I will move things to separate lines to make it easier to read and make sure I have the correct number of parentheses.

POWER(
 (
  SUM(`totalreported`) / SUM(`totalpaid`)
 )
 ,.2
 )-1

user026529

But the formula in beast mode validates just fine

MarkSnodgrass

I have found that to misleading at times. If you move your cursor to the first parentheses after the sum, you should see which closed parentheses it is linked to. Due to order of operations, it is going to try and evaluate your revenue field divided by the sum of your other revenue field. I'm pretty sure that is not what you are intending based on what you described earlier.