6 Replies Latest reply on Mar 5, 2018 9:07 AM by siva kumar

    Compare Windows_Max with Max function data

    siva kumar
    siva kumar Mar 4, 2018 3:29 AM

      Hello Experts

       

      Good Day

       

      I got a assignment to pick the first and last customer name depending on the length of the customer name,

       

      So my logic:

       

      Get the length of the customer name filed (Using LEN function) and then pick first and last record of the length column (Using Windows_Max and Windows_Min table calculations).

       

      Now compare the windows calculation with the legth column and pick the corresponding customer name. This is where I am struck when comparing I am getting error Cannot compare aggregate and non aggregate funcitons.

       

      Either my logic is wrong or I am comparing in wrong way.

       

      Attaching the workbook, Please help

        • 1. Re: Compare Windows_Max with Max function data
          Zhouyi Zhang
          Zhouyi Zhang Ambassador Mar 4, 2018 3:44 AM (in response to siva kumar)

          Hi, Siva

           

          Try my solution below by converting your table calculation to LOD

           

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Compare Windows_Max with Max function data
            Jim Dehner
            Jim Dehner Ambassador Mar 4, 2018 4:01 AM (in response to siva kumar)

            Hi Siva

             

            The message you are getting results when one of the arguments in an expression is aggregated and another is not - it is not an indication that the formula wont do what you want - only that the formula is at 2 different levels of aggregation -

            if you look in the calculated field drop down under "Aggregation" you can see all the functions that result in an aggregation

             

             

            in your case MAX is an aggregating function so Tableau expects the other values to be aggregated - Len is not an aggregation function so you can use a function like Attr(), Min() Max() - just wrap the Len() in any

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Compare Windows_Max with Max function data
              siva kumar
              siva kumar Mar 4, 2018 9:42 AM (in response to Jim Dehner)

              Thank you very much for response JIM.

               

              Changed the calculation as per your suggestion but still same error, I guess I am doing it in a wrong way or unable to understand your suggestion.

               

              Can you change the workbook and attach so that I can check where I am going wrong.

               

              Captureadsf.PNG

               

              First measure added Max to code in calculated field and in last measure added MAX as a part of aggregation

               

              Sir, though issue is resolved I am still interested in your solution, Please help me understand your suggestion.

               

              Thanks

              Siva

              • 4. Re: Compare Windows_Max with Max function data
                siva kumar
                siva kumar Mar 4, 2018 9:40 AM (in response to Zhouyi Zhang)

                Hi ZZ

                 

                Your solution works perfectly and I am able to get what is required. Thanks for that but I have few questions please help me to understand.

                 

                This code doesn't produce any errors (Your code):

                 

                { FIXED : MAX(LEN([Customer Name]))}=LEN([Customer Name])

                 

                But this code produces errors Cannot compare aggregation and non aggregation (I tried to change a bit)

                 

                { FIXED : MAX(LEN([Customer Name]))=LEN([Customer Name]) }

                 

                Only difference is comparing inside LoD and outside LoD, why a comparison produce errors inside LoD and why it doesn't produce any errors outside LoD

                 

                Can you please help me in understanding this:

                 

                Thanks

                Siva Kumar

                • 5. Re: Compare Windows_Max with Max function data
                  Zhouyi Zhang
                  Zhouyi Zhang Ambassador Mar 4, 2018 3:45 PM (in response to siva kumar)

                  Hi, Siva

                   

                  As you see, Max() is an aggregation while len() is not, so you can't compare these two because they are in different level. That's why LOD has been introduce for this case, the result of LOD function actually generate the same result for each row in the same detail level, and then you can compare len() with LOD in the same row level.

                   

                  ZZ

                  • 6. Re: Compare Windows_Max with Max function data
                    siva kumar
                    siva kumar Mar 5, 2018 9:07 AM (in response to siva kumar)

                    Hi ZZ

                     

                    Thank you very much for your explanation, Can't it be done using table calculation (Like I was doing before) without using LoD as filters.

                     

                    Thanks

                    Siva Kumar