1 Reply Latest reply on Apr 6, 2012 2:47 AM by Jonathan Drummey

    Show only most recent months and difference in Dashboard

      I need to do a comparison between March of this year with March of last year, but in the dashboard I would only like to show the values of March 2012 and March 2011 and their amount differences with the March of the previous year.  The user should also be able to go back to February or any other previous month dynamically in the dashboard. 

       

      Hiding the columns is an option that gives me the table I would like to have, but I can't see how to make it dynamic. 

       

      I've created a parameter to only show March 2012 and March 2011 and I could get the difference between March 2012 and March 2011 with a table calculation, but how do I get the difference between March 2011 and March 2010 if March 2010 is not shown? 

       

      Thanks for the help,  Karl

        • 1. Re: Show only most recent months and difference in Dashboard
          Jonathan Drummey

          Hi Karl,

           

          You were on the right track thinking about a filter, however most Tableau filters act on the data pre-calculation, so the data isn't availble for the year over year calcs. Filters on Table calculations, on the other hand, are applied after other calculations so you can set one up to show or hide the data.

           

          I first created a parameter to let the user pick how many months back, just to test it out. You might switch this back to a date parameter if you want, the next calc would need to change.

           

          Here's the Show/Hide calculation I created:

           

          LOOKUP(ATTR(IF DATETRUNC('month',[Order Date]) = DATETRUNC('month', DATEADD('month',-[Choose Months Back],NOW()))

              OR DATETRUNC('month',[Order Date]) = DATETRUNC('month', DATEADD('month',-[Choose Months Back]-12,NOW())) THEN

              "Show"

          ELSE

              "Hide"

          END),0)

           

          LOOKUP() is a table calculation function, it needs to be given an aggregate, so we wrap the IF statement in an ATTR(), and the 2nd argument to lookup is the row within the partition, so LOOKUP(foo,0) like we're doing returns the current value.

           

          Drag that onto the Filters shelf and select Show, and you should be all set. I've attached the workbook I created this in.

           

          Jonathan