7 Replies Latest reply on Jun 23, 2017 3:17 PM by Shinichiro Murakami

    Running Total by Rank v Order

    Zhenya Egupova

      Hello everybody!

       

      My issue concerns running total. The idea is similar to Pareto distribution. I have created a data set with Dimensions: "Category", "Year" and Measure: "Volume". Volume can be aggregated as sum by either or both categories.

       

      In the attached workbook, I created ranking field based on volume in descending order, so that highest volume is ranked as 1. Then, I am trying to create running total based on the ranking, so that Rank 1 = Volume 1, Rank 2 = Volume 1 + Volume 2, Rank 3 = Volume 1 + Volume 2 + Volume 3 and so on. The problem is that I can only do that if I sort the table on rank in ascending order. As soon as I change order to something else, running totals are off because Tableau starts summing up from the first row.

       

      I tried to introduce R script (Measure: R Running Total) but I am not very advanced in R-Tableau integration so I don't know how to make it work. Here is the code I came up with:

      SCRIPT_STR(

           'n <- .arg1

           summ <- 0

       

       

       

           for (i in 1:n) {

                    summ = summ + .arg2

           }

       

       

           summ',

      [Rank1],SUM([Volume]))

       

      Could you please help and let me know if running total calculation is possible base on ranking rather than simple line order. Please see the workbook with sample data and pre-calculated metric attached. The data is now in random order.

       

      Thank you very much in advance!