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

user026529
user026529 Member
in Magic 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 Coach

    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?

    **Check out my Domo Tips & Tricks Videos

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

    No the revenue columns dont have null values

    image.png

    see the screenshot.. its not validating itself

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

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

    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 Coach

    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.

    **Check out my Domo Tips & Tricks Videos

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

    It doesnt compute when I do the same in Beast mode


    image.png
    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach

    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
    **Check out my Domo Tips & Tricks Videos

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

    But the formula in beast mode validates just fine


    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

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

Categories