2 Replies Latest reply on Feb 19, 2014 2:33 PM by Sarah Aiello

    How can I show only the most recent value?

    Sarah Aiello

      Hi everyone,

       

      I have a record of monthly donations to a charity by individual.  Information is only recorded when users change their donation amount.  So, I have data which looks like this:

      What I would like to do is carry this information forward in Tableau so that whatever the last known value was for an individual it is carried forward from month to month until they change their donation info.  So, in essence, tableau would show a table that looked like it was based on the below data (where orange highlighted rows are rows that are actually in the raw data).

       

      I can't do a running sum or a running average because I actually only want whatever the most recent value was to show (I don't care at all about former values).  I can't do a running_min or a running_max because I don't know if the most recent value will be the max or min (it is possible to both increase or decrease the donation amount).

       

      Any ideas how I can accomplish this in tableau without restructuring the underlying data?

        • 1. Re: How can I show only the most recent value?
          Joshua Milligan

          Sarah,

           

          You can do this by using domain padding.  Domain padding happens in Tableau when you tell Tableau to "Show Missing Values" for dates or bins (right click the field in the view to get the option).  You can use certain table calculations to fill in these missing values.

           

          Here, I created the following calculation:

           

          IF NOT ISNULL(SUM([Donation Amount]))
          THEN SUM([Donation Amount])
          ELSE
              PREVIOUS_VALUE(SUM([Donation Amount]))
          END
          

           

          Here it is in action:

          1.png

          So what it is doing is working across the table left to right.  If the Donation Amount is not null (missing), then it uses that value.  Otherwise, it uses the previous value (effectively pulling the value across the empty spaces until a new value is reached).

           

          There are definitely other options to solve these kinds of problems.  If you have the option of creating the data structure to fill in missing values in the back-end (such as you had in the sample workbook), you might consider using it as it will be a little easier to use in Tableau (see Jonathan Drummey's alternatives here: Count the number of leave days taken by weekday | Tableau Support Community)

           

          Regards,

          Joshua

          • 2. Re: How can I show only the most recent value?
            Sarah Aiello

            Thank you Joshua! 

             

            I actually wanted to go one step further and add up all the values for user names.  Ideally, I would have a sheet with only one row, that was the sum of all the monthly users values for that month.

             

            The problem is that after using your calculation (which I renamed Donation Calc).  I can't use a sum or average (since it's already aggregated)

             

            I tried using either a window_sum or a running_sum (although what I actually want is average), but I couldn't figure out a way to set up the partitions to give me the correct answers.  For instance, below is what I got when I did a running sum of the Donation Calculation by "down".

             

            Any ideas of how to do this in tableau (I tried using look up and index, but I couldn't figure anything out with those functions either.

            Thanks again for your help so far!