7 Replies Latest reply on May 17, 2018 7:15 PM by Jay Wee

    Constants For Calculation

    Jay Wee

      Afternoon All Tableau Gurus,

       

      I've created a calculated Percentile field based on user input Parameters.

      I've got another parameter for user to enter the Tolerance % of the Percentile.

      After manual calculating it, 0.5 Percentile will give 255, + 10% Tolerance on top of the Percentile will give  280.5, - 10% will give 229.5.

      What i'm currently stuck with is I need to select those rows that falls between 229.5 and 280.5.

      However, the calculation is throwing out a "Cannot mix aggregate and non-aggregate argument with this function" error when I enter:

       

      IF [Sales Target]>=([Percentile]-([Percentile]*[Tolerance])) AND [Sales Target]<=([Percentile]+([Percentile]*[Tolerance])) THEN 1 ELSE 0 END

       

      I've tried putting ATTR() in front of [Sales Target], but its giving me a wrong results.

      Attached is a copy of the workbook with this error message.

       

      Please advice and Thanks in advance.

        • 1. Re: Constants For Calculation
          Deepak Rai

          Check now Pl

           

          Thanks

          Deepak

          1 of 1 people found this helpful
          • 2. Re: Constants For Calculation
            Jay Wee

            Hi Deepak,

             

            Can I please find out whether did you use ATTR() instead?

             

            The expected results should be Sales Target between 229.5 and 280.5.

             

            Is it possible to make Tolerance Field to show 1 for those Sales Target between 229.5 and 280.5?

             

            Thanks

            • 3. Re: Constants For Calculation
              Deepak Rai

              You are comparing sum of sales you dont need attr

              • 4. Re: Constants For Calculation
                Jay Wee

                Hi Deepak,

                 

                In that case, can you please show me a calculation that will place 1 for Tolerance field for those Sales Target that falls between 229.5 and 280.5?

                 

                I've used the calculation above and kept on getting Cannot mix Aggregate and Aggregate.....  error message.

                 

                 

                Thanks

                • 5. Re: Constants For Calculation
                  Jay Wee

                  Hi Deepak,

                   

                  Managed to figure out the solution.

                   

                  Thanks for the help Buddy.

                  • 6. Re: Constants For Calculation
                    Deepak Rai

                    Good!!! Pl close the thread

                    • 7. Re: Constants For Calculation
                      Jay Wee

                      Hi All,

                       

                      Just in case someone encountered the same issue, here are the solution.

                       

                      Before I start, I convert [Sales Target] to Discrete. This is just to exposed any [Order Dates] that has more than one [Sales Target].

                       

                      For the [Percentile] calculated field, I've used: {PERCENTILE([Sales Target],[Parameters].[Percentile])}.

                       

                      Then I create a [Min] and a [Max] calculation (or you can incorporate this step into the final [Min Max] calculated field). This is just to check the expected value of what the minimum and maximum to be flag as 1 or 0 for the final [Min Max] field:

                       

                      [Min] calculated field: [Percentile]-[Percentile]*[Tolerance]

                      [Max] calculated field: [Percentile]+[Percentile]*[Tolerance]

                       

                      Based on this calculated field, my [Percentile] value is 255, [Min] is 230, and [Max] is 281.

                       

                      The last step is to put all of this into [Min Max] field using this formula:

                      IF [Sales Target]>=[Min] AND [Sales Target]<=[Max] THEN 1 ELSE 0 END

                       

                      Or this can be as this if you didn't create [Min] and [Max] field:

                       

                      IF [Sales Target]>=([Percentile]-([Percentile]*[Tolerance])) AND [Sales Target]<=([Percentile]+([Percentile]*[Tolerance])) THEN 1 ELSE 0 END

                       

                      Put this field into the filter and you're done!

                       

                      Hope this helps.

                       

                      P/S: Please note that if you want to do it based on specific [Order Date] or some other points, then you'll need to change the [Percentile] formula to {FIXED [Order Date] : PERCENTILE([Sales Target],[Parameters].[Percentile])}

                       

                      or if you have a filter that you wish to incorporate priors to calculating the Percentile, then do something in a line of this:

                      {PERCENTILE(IF Condition THEN [Sales Target] END,[Parameter].[Percentile])}

                       

                      Thanks.