7 Replies Latest reply on Jun 21, 2018 3:39 PM by swaroop.gantela

    Filter Bumpchart

    Jaya Sinnathamby

      I want to filter out data from a bumpchart when data for the last year is missing.

       

      In the above chart, DVB, should not appear.

       

      It would also be nice to have the Year axis at the top.

       

      Workbook is attached.

        • 1. Re: Filter Bumpchart
          swaroop.gantela

          Jaya,

           

          Apologies, I didn't read carefully.

           

          Please see if this is closer to the goal.

           

          It can be done with table calculations:

          [Max Date Region]=[Max Date All]

           

          Where both are the same:

          WINDOW_MAX(MAX(YEAR([Survey Date])))


          just have different table calculation settings:

          Max Date All is the max year in the whole data set.

          It is across all dimensions, without restarting.

           

          Whereas  Max Date Region restarts every region.

           

          I think you will still need to use the

          LOOKUP([MaxFilter],0)

           

          method to maintain the bump positions.

           

          Sorry for not reading fully.

          • 2. Re: Filter Bumpchart
            swaroop.gantela

            Jaya,

             

            Also here is a better method for moving the column headers to the top:

            Re: Moving Axes Labels to the Top

            • 3. Re: Filter Bumpchart
              Jaya Sinnathamby

              Copied the calculated fields you created and used as suggested, but not able to replicate.

               

              On the surface, this should be simple - if there is no data for 2018, filter out, but why is it so difficult! You seem to have it working, but I'm not able to.

              • 4. Re: Filter Bumpchart
                swaroop.gantela

                Jaya,

                 

                Apologies for not clarifying.

                 

                I suspect the difference may be in the details of the settings

                of the table calculations, particulary the nested calculations.

                 

                In the attached workbook in the Forum Thread, I have made a few changes.

                I created a Survey Date Year (using Custom Dates )

                and a parallel Survey Date Label Year (sorry it doesn't look like

                the other method for moving axis to the top works here).

                This allowed me to use these dimensions in the settings for

                the Table calculations.

                 

                So in this new workbook I have edited the Max calculated fields

                to have a default table calculation setting. I think these should transport

                over when copying the pill to a different workbook, (as long as

                the new Custom Year fields described above are in place).

                 

                Regardless, in the below image I show what settings I was using

                for each nested calculation.

                 

                Apologies also if you have already set them this way and it isn't working.

                Am happy to help troubleshoot.

                273637filterCalc.png

                • 5. Re: Filter Bumpchart
                  Jaya Sinnathamby

                  I am sorry, I don't understand how your solution works.

                   

                  My apologies for injecting the request for moving the axis to the top; it is not only a distraction, but has injected further complexity into the solution.

                  • 6. Re: Filter Bumpchart
                    Jaya Sinnathamby

                    The good folks at Tableau Tech Support provided a solution that is straightforward and works great:

                    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

                    To filter out the Regions with no data in the max date we will need to take the following steps:

                     

                    1. Create a calculated field that will find the Max date overall for the whole data set:

                     

                    { FIXED : MAX(YEAR([Survey Date])) }

                     

                    1. Create another calculation that will compare the Max date of each region to the overall Max date:

                     

                    { FIXED [Region]: MAX(YEAR([Survey Date])) } = [!MAX Date]

                     

                    1. Create final calculation that will filter the view:

                     

                    LOOKUP(MIN([!Region Has Max Date]), 0)

                     

                    The reason we need this extra calculation is because the view uses Table Calculations and therefore we need to filter the view without filtering the underlying data. If we want the table calculations in the view to be affected by the filter then we can simply drag the calculation from Step 2 onto the filter and select True.

                     

                    1. Drag the final calculation from step 3 onto the filter shelf and select True.

                     

                    Please see the two different sheets for the different results of applying step 2 as a filter or step 3 as a filter. The filtered rank is using step 2.

                     

                    Due to the FIXED expressions we are using to calculate the years there may be issues with filters since they are ignored by FIXED. If this is the case and we want to see the filters affect the view, we can apply them to Context by right clicking it and select Context.

                     

                    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

                     

                    I moved the x-axis header to the top by using dual-axis and editing out the bottom axis.

                     

                    Thanks.

                    • 7. Re: Filter Bumpchart
                      swaroop.gantela

                      Jaya,

                       

                      Glad you found a solution.

                      Sorry for overcomplicating.

                      All the best.