8 Replies Latest reply on Feb 18, 2016 7:27 AM by Shinichiro Murakami

Dynamic cummulative sum like excel

Hi. I have been trying to generate a formula that accumulate values in a dynamic way like excel do.

For example: I have a data source (first 2 colums), with a date field and values for each day. I would like to generate, in another table, an output (like 3rd and 4th colums) that accumulate, for the next 10 days, and put result the within the day where the sum begin.

I dont know if there is any expression that can do this job or if I need to create filter or parameter.

Español: Necesito ir acumulando de forma dinámica por 10 días e ir colocando esta suma en cada celda. Si el día es 5 de enero, acumular desde el 5-14 y colocar esta suma en el día 5. Para el día 6, acumular desde el 6-15, y así respectivamente. Agradezco si conocen alguna sentencia o método para lograr este resultado.

 Data Source Output Date Values Date Value Explanation 1/1/2015 1 1/1/2015 24 sum from day 1-10 1/2/2015 2 1/2/2015 24 sum from day 2-11 1/3/2015 3 1/3/2015 30 sum from day 3-12 1/4/2015 1 1/4/2015 32 sum from day 4-13 1/5/2015 2 1/5/2015 32 sum from day 5-14 1/6/2015 5 1/6/2015 32 sum from day 6-15 1/7/2015 1 1/8/2015 2 1/9/2015 3 1/10/2015 4 1/11/2015 1 1/12/2015 8 1/13/2015 5 1/14/2015 1 1/15/2015 2

I hope you can understand  and help me in this case.

Regards,

• 1. Re: Dynamic cummulative sum like excel

Hi Nestor - if you're going to be showing all of the dates like this, you should be able to use this calculation:
WINDOW_SUM(SUM([Values]),0,9)

2 of 2 people found this helpful
• 2. Re: Dynamic cummulative sum like excel

Ben beat me to it, but here is a workbook for you.  I did the explanation calc too.

9.2 workbook attached.

• 3. Re: Dynamic cummulative sum like excel

mmm I am in tableau 9.0. I can't open it. I will try updating my software and I will write to you!

• 4. Re: Dynamic cummulative sum like excel

9.0 attached.

shin

• 5. Re: Dynamic cummulative sum like excel

Here is the other calc - promise to mention your version next time?

Explanation

"sum from day " +str(index()) + "-" + str(index()+9)

• 6. Re: Dynamic cummulative sum like excel

promise to mention your version next time?

Fight the good fight

• 7. Re: Dynamic cummulative sum like excel

Thank you Adam, I saw the formula in your image and I could make the cummulative sum.

I am starting with Tableau, it is nice to see how fast users respond with solutions.

• 8. Re: Dynamic cummulative sum like excel

That's exactly right statement.

"biting at every cast" is what describe the situation appropriately in English ??

Shin