Previous Row + Following Row: Cumulative Sum

leeloo_dallas

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:

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.

GrantSmith

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:

Here's a sample of the output:

GrantSmith

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:

Here's a sample of the output:

leeloo_dallas

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.