Hi Barbara, can you share a packaged workbook? At least one of the fields in your calculation must already be aggregated, but I can't tell which one without seeing the workbook. My guess is that you just need to remove the MIN() around [Diff between Receipt and Ship], though.
I can't send a workbook with my actual data but I can try to create a report with some false data later today if I have time.
The Calc Days field is calculated in Tableau. It is Order Ship Date - Order Receipt Date. In my code example it is titled [Diff between Receipt and Ship]. I had to include the MIN() in order to get the pass/fail code to work around the "aggregate" issue.
How is your Calc Days field calculated? I did a DATEDIFF function to calculate the days between receipt and ship:
I created two parameters: one for min lead time and another for max lead time. Then I did a formula similar to yours for pass/fail:
This is the output:
Then, to do your count, do a FIXED LOD calculation so the numbers will change no matter how you slice and dice the information:
Is this what you're aiming for?
You can probably create a fixed LOD expression at the orderid level which will return a dimension providing the flexibility your looking for. Hard to say exactly how without understanding your data.
Thanks for the response guys!
These seem like good solutions but I'm still getting some errors. The datediff function is working fine. However when I put it in the Pass/Fail calculation I still get the "aggregate" error unless I put MIN in front of the fields.
And, with the now valid "pass/fail" code the count function is giving me the following error.
Are you using a parameter for your Max and Min Lead Times? If you do, then you won't need to wrap the fields in the Pass/Fail calc in MIN. Plus, it allows greater flexibility if you want to change the lead times.
You're getting the error because the Pass/Fail calc fields are aggregated (using the MIN function), which results in a Measures field. So when you try to do the final LOD calc, it won't work since LODs need a Dimension for the first part of the formula.
The min and max lead times are two separate fields coming in from the data source. Is there a way to include the Min and Max in the pass/fail calculation without turning them into Measures?
As you can tell I'm pretty new at Tableau so I appreciate the help.
I would think that if they are coming in from the data source, you could use a formula like
IF [Weeks between Ord Rcpt and Final Plt Ship Date] > [LA Data (Lane Availability benchmark data)].[Max Lead Time] THEN 'Fail'
ELSEIF [Weeks between Ord Rcpt and Final Plt Ship Date] < [LA Data (Lane Availability benchmark data)].[Min Lead Time] THEN 'Fail'
If that doesn't work, is it possible for you to attach a packaged workbook that we could take a look at?
This is still giving me the "aggregate" error. I think I'll submit this to the Tableau support team and see if they can walk me through it. Once I get an answer I will update this with the solution they came up with.
Thanks for your responses it has been really helpful!