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

# Running Total by Rank v Order

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!

• ###### 1. Re: Running Total by Rank v Order

Hi Zhenya

I hope this helps.

Thanks,

Shin

• ###### 2. Re: Running Total by Rank v Order

Hi Shin, thank you very much for your response. It is definitely a new option that I have not tried before but I am not sure if it solves my problem (and I am sorry if I am not explaining properly). I will try to simplify and remove "year". SO now table looks like this:

Right now running sum adds values as a+b+c+d+e, i.e. table down. And I would like to add volume values in order of ranking: e+a+c+d+b. It is important however, that summation is done irrespective of table sorting. In other words, can you achieve e+a+c+d+b sequence without re-sorting the above table. Ideally the table should look like this:

Thank you!

• ###### 3. Re: Running Total by Rank v Order

Zhenya,

You mean this?

Exact same concept just removing year.

Thanks,

Shin

• ###### 4. Re: Running Total by Rank v Order

Hi Shin, I am sorry, I got lost in my own data dimensions on the first round. It looked to me that sum would restart in every category. But it helped to simplify the example. Thank you very much!

• ###### 5. Re: Running Total by Rank v Order

Zhenya

So you mean the problem solved?

If so, could you mark my ansswer as correct to close the thread.

Thanks,

Shin

• ###### 6. Re: Running Total by Rank v Order

Hi Shin,

Sorry, it was my first post so it took me a while to figure out what to do.

Thank you once again for your help!

Zhenya

• ###### 7. Re: Running Total by Rank v Order

Zhenya,

No worry.

Table calc is one of the most challenging area, you can learn more and more as you try more.

Thanks,

Shin