5 Replies Latest reply on Sep 20, 2016 11:46 AM by Bill W

    Overall Max of Values based on a Dimension

    Bill W

      Afternoon Tableau friends!

       

      I have an interesting issue I am trying to solve. I've tried a few things, LODs and Window_max function yet no avail.

       

      What I am trying to accomplish, is display the Max value within of the data based on the 'Team' dimension and the max value of the most recent month selected when I filter on the Year / Month. Then show max value of the Overall Rating that will display on each field.

       

      So, for example if 201601 was selected, I would want to see 2's cascaded down in Team A and 3's cascaded down in Team B.

      Also, if 201601 and 201602 was selected, I would want see  4's cascaded down Team A and 0's cascaded down Team B

      If 201601,201602 and 201603 were selected, I would want to see 4's cascaded down Team A and 5's cascaded down Team B.

       

      Basically taking the max value of the month and applying the max value of the overall rating that is incorporated in that month to all the other rows.

       

      Appreciate the collaboration.

       

      Note: Updated 9/20/16 - See v2 of workbook for corrected data and what I am seeking.

       

      -Bill

        • 1. Re: Overall Max of Values based on a Dimension
          Ivan Young

          Hi Bill,

          I think your issue can be solved by a context filter. Attached is a modified twbx.

           

          I created a fixed LOD for team { FIXED [Team] : MAX([Overall Rating]) } which I used as my measure, then I added the year/month filter to context.

           

          Let me know if you have any questions.


          Regards,
          Ivan

           

          1 of 1 people found this helpful
          • 2. Re: Overall Max of Values based on a Dimension
            Bill W

            Thanks for the reply, this was extremely helpful.

             

            However, I forgot to include one thing in the data set to account for an issue I am having. I'd also need to it factor base on the most recent value/max  in (i.e. Year/Month) as well.

             

            Here is the updated workbook.

             

            So for example if 201601 and 201602 are selected. I would want to see 4's cascaded down Team A and 0's cascaded down Team B.

             

            I apologize that my original dataset did not represent the whole problem.

             

            -Bill

            • 3. Re: Overall Max of Values based on a Dimension
              Bill W

              So, I figured it out if anyone is interested.

               

              First, change the date filter to Context.

              Second, create a set and that references the Date dimension and set it to Top 1, by the Date at the Maximum.

              Third, create an If then statement that references the Team and the Date:

                   If [Set 1] then {FIXED [Team], [Year / Date] : MAX([Overall Rating])} else null

                   END

              Fourth, drag it to the view and change the calculation method of the pill to Max. and you'r good go.

               

              Round about way of doing it lol :-).

               

              -Bill

              • 4. Re: Overall Max of Values based on a Dimension
                Ivan Young

                Hi Bill,

                Attached is a solution with arguably a less roundabout way of accomplishing your desired output.  It basically involved creating an additional LOB based filter which I called Max Date Filter: { FIXED [Team] : MAX([Year / Month]) } = [Year / Month] and modifying the measure LOD: {FIXED [Team], [Year / Month] : MAX([Overall Rating])}.

                 

                With this method you shouldn't need to use sets.  Let me know if you have any questions.

                 

                Regards,
                Ivan

                 

                1 of 1 people found this helpful
                • 5. Re: Overall Max of Values based on a Dimension
                  Bill W

                  That's Ivan, that's definitely a better way of doing this LOL :-).

                   

                  Unfortunately, now they tell me they don't want the max values per month selected. They want the most recent metrics, but still have the ability to use the year / date calculation to go back in time.

                   

                  I am confused how to do this. Because I would think when they select a year / date that's not a previous month then you can't return those values in the previous month.

                   

                  For example if the user was to select 201601 they'd want to see 201603 results in the view as well.

                   

                  Posted a question on that situation as well - Display a value that's not selected on a filter.

                   

                  -Bill