Tableau Version: 10.0.6
I'm hoping someone can point me in the right direction with returning a value using a calculated field.
Perform a Heavy, Medium, Light analysis on order size year-on-year; where Heavy = Top 20% of orders by number of units, Medium = next 30% of orders by number of units, Light = the remaining 50% of orders.
i.e. list all orders and the total units for each order, sorted by most units to fewest units. Create a % of running total of units for those orders, then determine the smallest number of units in an order which falls into the top 20% (the cut-off for "Heavy"). Then do the same for the next 30% of orders, to determine the cut-off point for "Medium". The rest will be "Light".
The end goal would be to see a chart showing, for each of the "Heavy", "Medium" and "Light" sets, what the cut-off points (number of units) were year-on-year - to analyse order sizes.
I have created a Pareto Chart (for a single year, which can be amended using a filter), showing % of Running Total of Units (y-axis) against % of Running Total of Countd of Order ID (x-axis).
I have then created the following calculated fields, in order to display a vertical reference line which intercepts at 20% of the running total of Units and at 50% of the running total of Units:
% of Orders = RUNNING_SUM(COUNTD([Order ID])) / TOTAL(COUNTD([Order ID]))
% of Units = RUNNING_SUM(SUM([Units]))/TOTAL(SUM([Units]))
20% Vertical Reference = IF [% of Units] <= 0.2 THEN [% of Orders] ELSE NULL END
50% Vertical Reference = IF [% of Units] <= 0.5 THEN [% of Orders] ELSE NULL END
I then dragged in a reference line, and set the Value to the Maximum of 20% Vertical Reference (and repeated for the 50% Vertical Reference).
This works fine, and I can see on my chart where the cut-off is for the Top 20% and next 30%; however, I am currently unable to return the value for the number of units at these intercepts - only the value for the % running total of countd of orders!
Is it possible to return, for each year, what was the smallest number of units in an order, from all those orders which fell into the Top 20% of orders (based on total units in each order) for each year?
If I list all the information in a table, it's easy to see what the answer is; however, I'm unable to created a calculated field (which Tableau accepts), to return the correct answer. The following did not work:
IF [% of Units] <= 0.2 THEN MIN([Units]) ELSE NULL END
But I don't know why!?
Any help would be greatly appreciated!