5 Replies Latest reply on Feb 13, 2018 1:08 PM by Scott Henry

    Find MAX(value) at different levels of detail, return string where category is max

    Scott Henry

      Hi all!

       

      My goal is to show which dimension value has a MAX(value) at a lower level of detail. I have a store location in the view, but not the sale category. I started by just attempting to get the MAX(value) for the category dimension not in my view, which was not terribly difficult - that is, MAX( { INCLUDE [sale category] : SUM([sales]) } )

       

      So now I have the amount for the category at the location that had the most sales. But I want the name of the category too, since I want to provide that info to the user along with it. I think I need to compare each category to the category max that I found, but I can't seem to figure out how to fetch the string back where the amounts match.

       

      Any ideas?

        • 1. Re: Dimension value
          Simon Runc

          Hi Scott,

           

          So with this kind of thing, it's often easier to do with FIXED LoDs as we can create these at a row level, so don't need to worry about the VizLoD.

           

          I recreated your Max of Category

          [Max of Category: FIXED]

          {FIXED [Location]: MAX(

          {FIXED [Category], [Location]: SUM(Sales)}

          )

          }

           

          and then used this to get the Category Name

          [Max Category Name]

          {FIXED [Location]: MAX(

          IIF({FIXED [Location], [Category]: SUM([Sales])} = [Max of Category: FIXED],[Category],NULL)

          )

          }

           

          If you are only interested in the Max Category, you could use your original calculation in a filter (and then use the Category Name), but by doing it the way I've done you don't need to filter out any of the data (so SUM of Sales is still the total of the location, for example)

           

          I also think you have the sales and max category sales against the wrong label in your sheet

           

           

          Hope that does the trick, and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Dimension value
            Scott Henry

            Simon thanks so much for your help! This was just what I needed!!! I'm doing my best to wrap my head around the nested fixed calculation, those are all together new to me!

             

            The first calc: so this will effectively always return the max sales of the category at the level of the location.

             

            The second calc tests whether that value is the same as the sum of the sales at that same level of detail, location-category, and if so, returns the dimension category rather than the measure.

             

            I do see one small issue, not with the solution, but with the desktop app, it appears to be a bug in the calc syntax markup/support. In the IIF block, the first argument is the nested fixed calc, but, when I go past the first comma, the calc help dialog (probably not the proper name for it) says I'm on the SECOND argument of the IIF, but I'm not. Adding a second paren around the fixed calc fixes this. Guess it's minor, but definitely confused me at first.

             

            Notice my cursor location versus the help dialog:

            Thanks again, you have been so helpful. This is a good strategy for lots of future use cases.

            • 3. Re: Dimension value
              Scott Henry

              Well - I ran into a few other issues with the solution immediately when running this on my bigger data set.

               

              1 - for some locations, the Max Category Name is NULL even though there is a Max of Category value.

               

              2 - for my dashboard, the user can set a relative date range. That breaks the calculation. I'm not sure how to consider the user defined time, for instance, during the past month.

               

              I'll try to get a new workbook with a larger data set and maybe there's some help out there!

              • 4. Re: Dimension value
                Simon Runc

                hi Scott,

                 

                Glad it (broadly) works. Not sure on the NULL categories, if you can get me over some data, with an example of when this occurs, I'll take a look.

                 

                On the filtering. FIXED LoDs are computed before any (regular) filtering is applied, so the values won't update when you filter. However you can get around this by making your filter "Context" filters

                 

                This has the affect of bumping the filter up the calculation pipeline, and so in applied before the LoD is computed.

                1 of 1 people found this helpful
                • 5. Re: Dimension value
                  Scott Henry

                  Thanks again Simon,

                   

                  The context filter works beautifully. Re: the NULL values, I think this was one of those little Tableau blips that disappears after refreshing thankfully! This has been so so helpful, context filters were never on my radar, until now! You're a star.