5 Replies Latest reply on Apr 27, 2012 10:45 AM by Jonathan Drummey

    How to dynamically display most recent month running totals

    Victor Pham

      All,

           We have a metric call "# of falls" that we are tracking. In Tableau, I am trying to report 2 different measures for that one metric. One is the total # of event across all month. The second is average of PtDays across all month. My data is represented by Fig. 1.  What I want the result to be is represented by Fig. 2.  I used Running_Total(SUM([# of Event])) computing across month to achieve the 74. I then did Running_AVG(SUM[PtDays)) computing across month to achieve the 2300.  I also created a variable name reporting_month and did a max([Month]) and stuck it on the Pages Shelf so that only the final results for the most recent month is displayed. However, when I close the workbook, it likes to reset itself back to the very first month of the dataset. I was wondering if there is a way in Tableau to dynamically force the worksheet to display the running totals for the most recent month without using the Filter shelf. Is that possible?

       

      Fig. 1

       

      YearMonth# of EventPtDays
      20121182300
      20122222300
      20123162300
      20124182300

       

       

      Fig. 2

       

      MetricTotal # of EventsAverage PTDays
      Fall742300
        • 1. Re: How to dynamically display most recent month running totals
          Jonathan Drummey
          Hi Victor,
          Based on your description, I'm not sure what pills you had in the view for your compute using for your table calcs? I ask because the default when using the Pages shelf is to show all values for the displayed dimensions and I'm not sure how you got the running sum to work.
          In the attached workbook in the "Alternative" worksheet I set up a view up that meets the display requirements is faster than a Pages shelf version. My understanding is that when the Pages shelf is used, Tableau has to compute all results for all dimensions/rows. In this version, the measures are surrounded by IF LAST()==0 THEN [calculation] END, so the measures are only calculated for the last row in the partition, which is the latest month. Additionally, there is a "Latest Month Filter" which is IF LAST()==0 THEN "Show" ELSE "Hide" END. If you're not familiar with them, filters on table calculations are applied after all other calculations are done so they effectively hide other results, so that way we're not showing the other months and have all records available for the calculation.
          Jonathan
          • 2. Re: How to dynamically display most recent month running totals
            Victor Pham

            Hi Jonathan,

                     The "LAST" function was my missing link. I was not aware that you can use that function that way. Thank you. To answer your question, we had the variable "Month" in the Level of Detail to use in the "Compute using". Also, in your replication of my description, you had the full datetime where we have month number as a variable and year as a variable. No date string. I myself don't understand it very well because this report belong to someone else and I was asked to improve it. I thought there was a better way to do it but could not figure it out so I posted a question on here. Thanks again.

            • 3. Re: How to dynamically display most recent month running totals
              Victor Pham

              Jonathan,

                   I  have another related issue. We have other worksheet that show more details that require us to use column Grand total. When you apply that to the example you attach, the RA # of Records is incorrect. Is there a way to manipulate the Grand total numbers too?

              • 4. Re: How to dynamically display most recent month running totals
                Victor Pham

                Jonathan, applying your example to our detailed workbook is producing another issue. It doesn't allow us to add dimensions as an added level of details. Using your example, if I want to show the total by date and for each zipcode, is that possible? I tried and it only display the last zipcode in the dataset due to the "Last" function.

                • 5. Re: How to dynamically display most recent month running totals
                  Jonathan Drummey

                  Hi Victor,

                   

                  When you are adding dimensions to overall level of detail i.e. to the Rows, Columns, or one of the shelves on the Marks card, you need to change the table calculation to work at the appropriate level of detail. Since you want to have the calculation partitioned along the dimensions you are adding to the view, you will need to re-set each of them (including the filter) to Compute Using Month. Often what I'll do in this case is take the filter off, make sure the right rows are getting the right numbers, then put the filter back on again.

                   

                  As for the Grand Total, those are made of separate calculations that Tableau performs at the appropriate level of detail of the view, so when you have a table calculation that is already working at varying levels of detail then the Total can *appear* to not line up numerically with what else is being displayed in the view. In other words, the Totals are logical from Tableau's point of view, but not necessarily us humans. I've created an Idea to have the totals work based on what we can visually see in the view, if you're interested vote it up here:

                   

                  http://community.tableau.com/ideas/1232

                   

                  That said, there is a workaround to hack your own Grand Total calc, however it uses undocumented functionality in Tableau and is very sensitive to the dimensions in the view, I did a write up on it here:

                   

                  http://community.tableau.com/thread/116854

                   

                  Jonathan