CODEWITHSUNDEEP
tableau-api
Rob McCauley
Rob McCauley

Reputation: 538

Aggregate an Running Sum in Tableau?

I have a simple data set showing the number of points scored by a player. It is plotted across a [Week Number] x-axis. I added a Running Sum calculation, as a Line, split by Player (color).
Running Sums by Week

For each week, I would like to identify the top player, by total points achieved to-date.
For example, in Week 3, the top player was GREEN with a running total of 8 points.

I tried creating a LOD function {FIXED [WEEK]: MAX(RunningSum)} but it complains that I cannot aggregate an aggregate.
I tried WINDOW_MAX(), RANK(), and tried adjusting the Table Calc, Advanced, Partitioning and Addressing feature.

Is there no way to identify the MAX of a collection of running totals, by discrete [Week Number]?

This can be done fairly easily in TSQL by wrapping a query in outer queries and applying Window functions with partitions defined.

Upvotes: 4

Views: 18657

Answers (1)

Andrew LaPrise
Andrew LaPrise

Reputation: 3413

You can absolutely just use RANK(), you just need to make sure the table calculation is being computed using Player. I imagine your [RunningSum] field looks something like this:

RUNNING_SUM(SUM([Points]))

Create a table calculation (let's call it [Player Rank]):

RANK([RunningSum])

Then we need to edit the table calculation. You can do this directly from the Calculated Field edit window (there's a blue link above the OK and Apply buttons that says something like "Default Table Calculation").

We want to compute the rank of our players, so we'll go into "Compute Using:" and select Player.

For a quick sanity check, drag [Player Rank] into Tooltip and check it out.

Upvotes: 6

Related Questions