filtertableau-apicalculated-fieldrolling-computationlevel-of-detail

Running Sum of Filtered Rows in Tableau


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

enter image description here

enter image description here

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

enter image description here

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:

enter image description here


Solution

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

    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

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

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

    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

    enter image description here

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

    enter image description here