CODEWITHSUNDEEP
tableau-apicumulative-line-chart
Trexion Kameha
Trexion Kameha

Reputation: 3580

Create Cumulative Change Chart in Tableau

I have a bunch of daily change % data. I would like to calculate cumulative change, which should just be (1+change)*previous day in a chart in Tableau.

Seems simple enough right? I can do it in a few seconds in Excel, but I've tried for hours to get it to work in Tableau and cannot do it.

My thought was that I can create a column that is (1+daily change%), then try to do a compound product. However, I can't seem to get it to work.

I can't attach any files here so I pasted the data, along with a column that is "cum change", which is what I would like the calculation to be.

Thank you much in advance!

            Date    Daily Change    Cum Change
            4/1/2015    0.47%   1
            4/2/2015    0.56%   1.0056
            4/3/2015    -0.72%  0.99835968
            4/6/2015    -0.56%  0.992768866
            4/7/2015    -0.80%  0.984826715
            4/8/2015    0.44%   0.989159952
            4/9/2015    -0.66%  0.982631497
            4/10/2015   0.99%   0.992359549
            4/13/2015   0.92%   1.001489256
            4/14/2015   0.73%   1.008800128
            4/15/2015   0.95%   1.018383729
            4/16/2015   0.42%   1.022660941
            4/17/2015   0.52%   1.027978778
            4/20/2015   0.02%   1.028184373
            4/21/2015   0.56%   1.033942206
            4/22/2015   0.35%   1.037561004
            4/23/2015   -0.34%  1.034033296
            4/24/2015   0.18%   1.035894556
            4/27/2015   0.61%   1.042213513
            4/28/2015   0.46%   1.047007695
            4/29/2015   0.94%   1.056849568

Upvotes: 1

Views: 1989

Answers (1)

Andrew LaPrise
Andrew LaPrise

Reputation: 3423

Create a calculated field:

IF INDEX() = 1
  THEN 1
ELSE 
  (1 + AVG([Daily Change])) * PREVIOUS_VALUE(1)
END

The condition checking to see if it's the first row of the partition (INDEX() = 1) is necessary to ensure that the first value of the field is a 1. After that, you can just use the self-referential PREVIOUS_VALUE() to get the previous value of this same calculation.

Upvotes: 2

Related Questions