9 Replies Latest reply on Apr 27, 2017 7:59 AM by Gerardo Varela

    Using WINDOW_SUM across date for each user ID, then aggregating?

    Julian Van Giessen



      This one has had me spinning my wheels all day. I'm fairly confident this can be done in Tableau, but have not been able to get one piece of the problem in place: the aggregation.


      The data: I have a table (connected to DB) of daily snapshots containing:

      • date: date of the snapshot
      • User ID: the unique user id
      • Deposit: a dollar amount of that days deposit (usually 0)

      E.g. a row of (1/1/2017, 123, 12.50) signifies that, on 1/12017, user id 123 DID have a deposit of 12.50


      The problem:

      I need to evaluate the distinct users who have had a deposit in the last 7 days, for each day.


      Current approach:

      I have a table with Date on the columns, User ID on the rows, and a filter with a calculated field called, "Has had deposit in past 7 days", which outputs T/F.

      The formula is:

      IIF(WINDOW_SUM(SUM([Deposit]), -6, 0) > 0, TRUE, FALSE)


      Here's a screenshot of the setup:

      Screen Shot 2017-04-25 at 5.40.00 PM.png


      I cannot seem to find a way to have tableau simply count the number of red boxes in each column in the screenshot above. If I COUNTD([User ID]), the filter looses sight of the [User ID] facet and returns True for all cells (thus counting everything, instead of just the users who have had deposits in the past 7 days).


      The viz I'm ultimately looking to create is a bar (or line) chart that essentially counts the number of blue boxes for each day in the screenshot above. Or in descriptive sentence form: for each day, how many users have had a deposit within the past 7 days. I've uploaded a twbx with a reduced data set that encompasses the issue. Thanks!