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).
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.
Answer
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.