In Tableau, how can I find the difference between the max of a set of numbers and the average of the numbers after the max number?

Question

I have a Tableau table as follows:

I'm trying to create a field which would return one value for each part number. That value should the max 'Qty' of the 5 weeks and subtract the average 'Qty' of the n weeks after the max 'Qty' week.

For example: For part A, the max 'Qty' is 6. The average of 'Qty' for the three weeks after the max is the average of 2,2, and 1 which is 1.7. So the number I am looking for here is 5-1.7 which is 3.3.

So the final solution would look something like this:

B is 0 because the max 'Qty' is 5 and the average 'Qty' taken after any 5 is 5 and thereby the difference is 0.

D is 0 because the max 'Qty' is 50 which is in week 6 and there are no values after that.

Answer 1

We'll start by setting the stage:

We'll then take this one step at a time.

1. Max Qty by Part

   MAX({ FIXED [Part]: MAX([Qty]) })

This calculation captures the max quantity for each part.

2. Week of Max

   IF([Max Qty by Part] = SUM([Qty])) THEN MIN([Week]) END

This calculation returns the weeks where the max equals the Qty for that week.

3. Earliest Max Week

   WINDOW_MIN([Week of Max])

Now we need to create a table calculation to populate all the rows for each part with the earliest week that matched the max.

You'll need to the edit the table calculation to match these settings.

You should see this if you've followed the steps above.

4. Is After Earliest Week of Max Qty

   ATTR([Week]) > [Earliest Week of Max]

Now we need to create a flag for those weeks that fall after the first week of the Max Qty.

Note that you may need to edit this table calculation to match the previous one

5. Average Quantity After Earliest Max Week

   WINDOW_AVG(IF([Is After Earliest Week?]) THEN SUM([Qty]) END)

This will average the Qty's after the earlies week where the Max Qty was met.

For this nested table calculation two calculations must be set.

You'll see this if you've been following along.

6. Your Magic Number

   [Max Qty by Part] - [Avg Qty After Earliest Max Week]

Now it's just a matter of subtracting our calculations.

Don't forget to set the table calculation

7. Hide what you don't want to see.

We'll use this calculation to help us:

FIRST()

Place that table calculation on the filter shelf and edit as we have the other. Then adjust the FIRST filter to 0.

Now you'll only see a single row for each Part.

We can hide the Week Header.

And remove all the measure except The Number.

Obviously, a lot going on here, but that'll get you what you're after. Hope this was helpful. Happy vizzing!