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

# Constants For Calculation

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.

• ###### 1. Re: Constants For Calculation

Check now Pl

Thanks

Deepak

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

Hi Deepak,

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

You are comparing sum of sales you dont need attr

• ###### 4. Re: Constants For Calculation

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

Hi Deepak,

Managed to figure out the solution.

Thanks for the help Buddy.

• ###### 7. Re: Constants For Calculation

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.