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

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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?

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • No the revenue columns dont have null values

    see the screenshot.. its not validating itself

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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

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

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • It doesnt compute when I do the same in Beast mode



  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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
    
    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • But the formula in beast mode validates just fine



  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.