
1. Re: Constants For Calculation
Deepak Rai May 10, 2018 11:41 PM (in response to Jay Wee)
Superstore Constant_v10.2.twbx 404.8 KB


2. Re: Constants For Calculation
Jay Wee May 10, 2018 11:46 PM (in response to Deepak Rai)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 May 10, 2018 11:49 PM (in response to Jay Wee)You are comparing sum of sales you dont need attr

4. Re: Constants For Calculation
Jay Wee May 10, 2018 11:55 PM (in response to Deepak Rai)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 May 17, 2018 6:22 PM (in response to Deepak Rai)Hi Deepak,
Managed to figure out the solution.
Thanks for the help Buddy.

6. Re: Constants For Calculation
Deepak Rai May 17, 2018 6:36 PM (in response to Jay Wee)Good!!! Pl close the thread

7. Re: Constants For Calculation
Jay Wee May 17, 2018 7:15 PM (in response to 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.