Adding Two Rows

Options
user08111
user08111 Member
in Magic ETL

I have a dataset listed below:

 

Year  Gain Loss Net

1999  10   5     5

2000  15  20   -5

2001  12  9      3

2002  19  8     11

2003  17  21   -4

2004  18  3    15

 

I need to create a new column that has a sum of all active clients for that year:

 

Year  Gain Loss Net Active

1999  10   5     5       5

2000  15  20   -5       0

2001  12  9      3       3

2002  19  8     11     14

2003  17  21   -4      10

2004  18  3    15       25

 

I can't seem to figure this out in ETL or BeastMode. 

 

Best Answers

Answers

  • rado98
    rado98 Contributor
    Options

    Rather than a beastmode you could use the running total option in the card builder (General Chart Properties). That is the quickest, most stream line way. Depending on the card type and fields used it might not be available.

    If you need to do it via ETL it becomes a tedious 10 step process. I saw a tutorial somewhere but never used myself.

  • user08111
    user08111 Member
    Options

    I can't seem to find that in any of the cards. I actually need the data to calculate % gain and loss. I did find this article but it doesn't show me how to this for years.

    https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Rolling_Average_Using_DataFlows

  • rado98
    rado98 Contributor
    Answer ✓
    Options

    The running total optino should be there, see bellow.

     

    If I had to do percent increased and so on I would try something that would use in part the method in this link

    https://dojo.domo.com/t5/Card-Building/Percent-change-WoW-MoM-etc-as-a-trending-line-in-card/m-p/22965#M2453

     

     

    Untitled.png

     

     

  • rado98
    rado98 Contributor
    Options

    Hi

     

    Try the "Lead" function in ETL a combination of that plus an addition, repeated 5 times will definetely give you what you need (provided that your dataset is indeed that simple.

     

    Cheers  

  • user08111
    user08111 Member
    Options

    This might be a dumb question but where is the "Lead" function?

     

    I don't see it as one of the options.

  • rado98
    rado98 Contributor
    Answer ✓
    Options

    Sorry I should have been more specific.

    In ETL it is part of the Rank & Window

    Check out the way to use it in the knowledge base

  • user08111
    user08111 Member
    Options

    That worked!!!!

     

    Thank you!!!!

Categories