Previous Row + Following Row: Cumulative Sum

leeloo_dallas
leeloo_dallas Member
edited May 2021 in Charting

Previously, I was provided a video to help with receive this result, but didn't realize that performing it in View Explorer didn't quite work, so I sadly had to create an thread for the same question.

Using Magic ETL I would like to find a way to take the previous row + the following row, keep the sum and continue to add the following row to the sum.

What I will eventually be able to do, is perform an 80-20 analysis. Typically, I would do this in excel, but I wanted to see if it can be accomplished in DOMO. I attached an example doc complete with my formulas still attached, in hopes that it would help.

What I would like to do is like the following:

Example:

Table

Date Name Value Result

10/01/20 A 1 1

10/01/20 B 2 3

10/01/20 C 3 5

11/01/20 D 1 1

11/01/20 E 2 3

11/01/20 F 3 5

Setting the preceding to unbounded and following to 1, I was able to achieve the results I wanted but with one teeny tiny issue: those first values:

image.png

Originally, there was an answer to do this in views explorer, but the issue with using view explorer is that it didn't treat my values as unique and just gave me a lump sum. When I export it, I lose all the rest of my columns and have to return and join it back in in magic ETL.

So I'm hoping for is how to get percentile rank in magic etl (only).

Super sorry for the repeated question. I intend to use the report to complete my result using the Analyzer, so completing it in Beastmode woke work since I'll need to filter on it.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @leeloo_dallas

    Taking your sample excel file from https://dojo.domo.com/discussion/comment/53717/#Comment_53717 and as they mentioned in that ticket, you can take the cumulative sum taking unbounded to 0 following to calculate the running total then divide that by the overall total to get your contribution percentage.

    I've attached the JSON representation of the dataflow I used to calculate this. You can copy this text and then paste it into a Magic ETL 2.0 dataflow.

    leeloo_dallas.txt

    Here's a screen shot of what it'll look like:

    Screen Shot 2021-05-25 at 10.57.47 PM.png


    Here's a sample of the output:

    Screen Shot 2021-05-25 at 10.53.30 PM.png


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @leeloo_dallas

    Taking your sample excel file from https://dojo.domo.com/discussion/comment/53717/#Comment_53717 and as they mentioned in that ticket, you can take the cumulative sum taking unbounded to 0 following to calculate the running total then divide that by the overall total to get your contribution percentage.

    I've attached the JSON representation of the dataflow I used to calculate this. You can copy this text and then paste it into a Magic ETL 2.0 dataflow.

    leeloo_dallas.txt

    Here's a screen shot of what it'll look like:

    Screen Shot 2021-05-25 at 10.57.47 PM.png


    Here's a sample of the output:

    Screen Shot 2021-05-25 at 10.53.30 PM.png


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • leeloo_dallas
    leeloo_dallas Member

    Wow that works great! I changed some things around and was able to get the cumulative sum using your JSON script.

    Thanks so much for your help, really appreciate it.

Categories