Running Sum of Filtered Rows in Tableau

Question

I have a table of challenge submissions (that records the time of submission of a challenge in a competition by different players, and whether the submission was correct or not) -

and another table that has the points associated with each challenge -

How do I plot a graph of running sum of points earned by the top 3 players in the competition over time (for last 24 hours only)? The catch here is that I only need to consider the first successful submission in case there are more than one successful submissions for a challenge in the competition (eg. Challenge #17 for Player A).

EDIT:

Dummy Data

Desired Output:

Can you please upload a csv sample here as a code? It looks like that it can be done — AnilGoyal
@AnilGoyal added dummy data and desired output to the question — harry04
I have proposed an answer/walkthrough, please see if it helps — AnilGoyal

AnilGoyal AnilGoyal · Accepted Answer · 2020-11-06T07:36:27

I am proposing a solution/answer assuming a few things-

Challenge acceptance time ends at 17:00 everyday
Different lines represent different challenges

Step-1 Create a CF to adjust date/time by calendar date - adjusted date as

DATEADD('hour', 7, [Date])

Note that I have added 7 hours to make the last calendar date/time for submission as 00 AM next day.

Step-2 Create another CF win_loss as

If [Success]='W' then 1 ELSE 0 end

step-3 create another CF game points as

[win_loss]*[Points (Points)]

Step-4 create another CF first win or loss as (don't worry about loss here)

{FIXED [Player], [Challenge], [success] : MIN([Date])} = [Date]

Step-5 create a set on 'players' field with TOP-3 with this formula (select top 3) by

sum(
IF [first win or loss]= TRUE
then [game points] END)

Step-6 build your view by dragging

set, MDY(adjusted date) & first win or loss on filters shelf/card
add mdy filter to context
[date] with exact date and discreet to columns
sum(game points) to rows
adding table calculation on measure - running total
right click sum(game points) click edit in shelf and replace the existing calculation by this one-

RUNNING_SUM(ZN(SUM([game points])))

(Note this will ensure your lines start at f(x)=0 always)

challenge on colors in marks card
sum(game points) to text in marks card.

Note- filters on (i) Set will ensure the top 3 players are in view only (ii) adjusted date will ensure view for 24 hour challenge submission time (iii) first win or loss will eliminate second and subsequent win(s) by same player for same challenge

I hope this will also make things clear to you.

You should get your desired view

OR change the date field to seconds to get a view like this

Running Sum of Filtered Rows in Tableau

1 Answers