4 Replies Latest reply on Sep 12, 2016 8:04 AM by Nicholas Vining

    Only Show Last Month of a Filter but Preserve Table Calculation

    Nicholas Vining

      Hi All,

       

      I'm relatively new to Tableau and trying to figure out how to show only the last month of a date range while preserving the table calculation. 

       

      Here's the set-up: I'm calculating the percent difference in traffic for 5 sites across a 3 month period.  There are 19 different traffic categories so I'm calculating the percent difference for all categories and then ranking them and displaying the top category.  I only want to show the last month so I just hide the other 2 months to preserve the table calc.  My problem is that if I alter the date range so that the last month is different those months are hidden and don't show up.  I've tried using [Monthcodes] == { FIXED: max([Monthcodes]) } but then the table calculation is invalid. 

       

      Any help is much appreciated! 

       

      Thanks,

       

      Nick

        • 1. Re: Only Show Last Month of a Filter but Preserve Table Calculation
          Alastair Young

          Nicholas,

           

          Without an example workbook this may be difficult to provide much help, However I believe I understand your problem.

           

          When you apply the hide, this would be hiding specific values like month names/numbers and thus when you move the date range it subsequently shows or drops these out accordingly. 

          What may be worth trying is to have a calculated field to provide a 'Current Month' and 'Previous Months' result  Extract the month value from your date selector or date range and assign this as your 'Current Month', with the two months prior to this value as your 'Previous Months' . When you drop this onto your workbook, you will see them segmented. This is when you can apply the hide to the 'Previous Months' result.

           

          Obviously this may have some affect on your current workbook, as well as the majority of your calculations however this allows the workbook to be dynamic against the date range.

           

          *** I would also be careful of the yearly crossover if using month numbers only.

           

           

          Thanks

          Al

          • 2. Re: Only Show Last Month of a Filter but Preserve Table Calculation
            Nicholas Vining

            Hi Al,

             

            Thanks for your response, this sounds like it should work but I'm having trouble extracting the month value from my date range. Can you provide me with that calculation?

             

            Thanks!

             

            Nick

            • 3. Re: Only Show Last Month of a Filter but Preserve Table Calculation
              Alastair Young

              Hi Nick,

               

              This is entirely dependant on your date.  Are you providing a Date Range or a single date ?  If you are looking at month on month, then giving the user a filter of Month and Year would work.

               

              If you are using a Range then you will need to code the max month out of that however I would question the reasoning why you would cross over months with a range, to then find the previous two months worth of data for the comparison?

               

              If you have a date range covering say 25th Aug - 5th Sep, Your current month would only show 1-5th Sep, with the August data being hidden as specified. So providing one single point for analysis would work much better. Having a chart showing the date range versus the previous 2 months would inherently incorrect as you are technically double counting, The dates between the 25th Aug - 31st would be in the active chart, As well as in the previous month data, However your request is for the max month.

               

              Can you maybe provide an example workbook or screenshot so I can understand your end goal more fully.

               

              Cheers

              Al

              • 4. Re: Only Show Last Month of a Filter but Preserve Table Calculation
                Nicholas Vining

                Hi Al,

                 

                I've figured it out! First, let me clarify and answer your questions:

                 

                I'm using a date range and looking at first month vs last month in the range.  The data is rolled up into a monthly aggregate so your example of August 25th to September 5th wouldn't apply here.

                 

                What I ended up doing was using the Last() function Last()==0 and computed it along months.  That way it still preserves the table calculation and only displays the last month in the range.

                 

                Thanks for your help Al.

                 

                Nick