6 Replies Latest reply on Oct 4, 2013 10:08 AM by Aran Bates

    Total of multiple fields logically compared to a percentage of the maximum total

    Aran Bates

      Hi there,

       

      For each customer I have 5 scores. I then total these scores using a calculated field to get [Total score]. Using this total score I want to categorise them based on how their total score compares to the maximum of all the total scores and another logical test.

       

      For example if the total score is less than 30% of the Max and another variable is above a cut off then categorise them as "Automated"

      IF (([Total score] <= (0.3*MAX([Total score]))) AND ([Number]>[Cutoff])) Then "Automated"

       

      There are then another number of ELSEIFs that come after to gategorise the rest but the error is the same for them. Namely that [Total score] can't be compared with the Max of [Total score] and I get the error message can't mix aggregate and non-aggregate arguments to function. The AND part of the calculation seems to be fine.

       

      The data for this is structured as follows

       

      score 1score 2score 3score 4score 5Total score (calculated in Tableau)
      53215[score 1] + [score 2] + [score 3] + [score 4] + [score 5]
      23121[score 1] + [score 2] + [score 3] + [score 4] + [score 5]
      67129[score 1] + [score 2] + [score 3] + [score 4] + [score 5]

       

      Many thanks in advance.

        • 1. Re: Total of multiple fields logically compared to a percentage of the maximum total
          Shawn Wallwork

          Aran, I'm going to guess that your Total Score calculation is something like: TOTAL(SUM([Score])). So it is an aggregate. Then when you try to wrap this in  MAX() you'll get an error about aggregating an aggregate. You might be able to use WINDOW_MAX, but I couldn't say without seeing a packaged workbook.

           

          If you do get the WINDOW_MAX to work, the next problem you'll need to tackle is aggregating the rest of the fields. It could look something like this:

           

          IF (([Total score] <= (0.3* WINDO_MAX([Total score]))) AND (SUM([Number])>SUM([Cutoff]))) Then "Automated" END

           

          But again I can't really tell you if it will work without trying it out, especially since it will now be a table calculation.

           

          --Shawn

          • 2. Re: Total of multiple fields logically compared to a percentage of the maximum total
            Shawn Wallwork

            Aran, looks like your employer altered your post:

             

            + + ...+


            Doesn't mean anything to me. Your best option is to create a packaged workbook using the Superstore dataset that came with Tableau to create a packaged workbook that is similar to the viz you are creating. Then I can help you get it working the way you want.


            --Shawn

            • 3. Re: Re: Total of multiple fields logically compared to a percentage of the maximum total
              Aran Bates

              Hi Shawn,

               

              Thanks for offering to take a look. I have attached a packaged workbook which recreates the issue that I am having.

              I have written out and saved the calculated field I am having issues with as I would have thought it could be written. It is the field [Category].

               

              The result is that I am aiming for is to bucket the customers into Group 1, 2 or 3 based on whether their total score falls into either the bottom 30%, middle 40% or top 30% of the range between the highest and lowest total scores (looking at every customer).

              Additionally, there is an AND piece of logic which deals with a manual cut off to push customers from Group 1 into Group 2 if they are over or under the cut-off. The cut off is a parameter that the user can control through a slider bar.

               

              Hope that makes sense, if it doesn't I apologise and will try to explain more clearly.

               

              Thanks,

              Aran

              • 4. Re: Re: Re: Total of multiple fields logically compared to a percentage of the maximum total
                Zac Hilbert

                Aran,

                 

                I think the attached does what you need it do. It may require some tweaks (wasn't sure weather to use >,<,>=,<=, etc. for some of the comparisons.  I split up the logic into several sub calculations.  As some of these are table calculations, you need to make sure you are calculating them at the correct level.  I believe the defaults (Table down) were the correct choices for each case here.

                 

                Briefly, I create two table calcs that compute the minimum and maximum Total Score (table down).  Using these, I calculate the ceiling of the bottom of 30% and the floor of the top 30%.  I compare the sum of the total score for each row to these ceiling/floor values (Group). Then I use conditional comparison of the Over-ride to the Manual_override_limit to decide if I need to add 1 to Group (Adjustment).  Then I convert Group + Adjustment to a string and append it to 'Group ' (Category).

                 

                Let me know if you have any questions.

                 

                Zac

                • 5. Re: Re: Re: Total of multiple fields logically compared to a percentage of the maximum total
                  Aran Bates

                  First of all thank you very much Zac, the answer worked and (I think) is proving to be quite helpful in my understanding of Tableau.

                   

                  With that in mind I hope you don't mind me asking if you could clarify my understanding to which I have  a few points.

                   

                  1) Looking at Bottom30Ceiling calculated field.

                   

                  Firstly, to clarify MAX([Total Score]). This is finding the max value on each row of [Total Score] and hence just returns the Total Score. Then by doing TOTAL(MAX([Total Score])) this finds the max value looking down the column of MAX(Total Score)? And this is because TOTAL() just does what ever the function inside the brackets is but at a higher aggregate level irrespective of the rows?

                   

                  2) Looking at Group calculated field.

                   

                  In here you have used sum ([Total Score]). This is because we need [Total Score] to be at an aggregate level in order for it to be compared with Bottom30Ceiling (or Upper30Floor) which is an aggregate field as it carries out a MIN and MAX function making it an aggregate field. By doing Sum([Total Score]) we are just summing along the rows in [Total Score], however as there is only one value in each row for [Total Score] it just returns the value in the row which is the same as the total score calculated. The reason for doing this was so that [Total Score] could be represented technically at an aggregate level and then be compared to Bottom30Ceiling.

                   

                  Hoping I have got those two points correct because if so then I understand what is going on and now understand what SUM() and TOTAL() are doing. However, if I have got that wrong would really appreciate being corrected.

                   

                  Many thanks,

                  Aran

                  • 6. Re: Re: Re: Total of multiple fields logically compared to a percentage of the maximum total
                    Aran Bates

                    Hi Zac (or anyone else who can help),

                     

                    I took your solution and altered it a bit to do the override logic at the initial classification stage (using the calculated field [Function]) and maybe this is where it is going wrong. It seems to work fine when the customer IDs are listed out as detail, such as rows in the table or as the Abc Label or Colour in a bar chart. However, if you want to have a bar chart where the different categories are the rows and the height is a distinct count (or just count) of the number of Customers, or where the colour is category then it fails to recognise any of the other categories other than the upper most category.

                     

                    I have a feeling this is to do with the way that Tableau is aggregating the calculated field [Total Score] when creating the bar chart but I can't seem to work out (no matter what I try) how to get it to not do this.

                     

                    I have created several sheet to try and highlight the issue to help with understanding a fix.

                     

                    Many thanks in advance,

                    Aran