December 22, 2017

Show Sales Lines Based on Profit Condition of Latest Month




This post describes a solution on how to show sales lines by category that meet minimum profit condition based on latest month.  The problem is interesting in that you need to combine multiple Tableau concepts (Level of Details and Order of Operation) in order to get to the solution.  Many thanks to Shinichiro Murakami for his helpful contribution to find the solution.


The solution has to meet following two conditions (1) Minimum Profit Level and (2) Latest month.


First, create a calculated field to filter for the latest month.




Second, create another calculated field to show profit based on latest month.




Third, create another calculated field to filter for latest month profit that meets minimum profit level.




Since we want to show the sales line over a range of Order Date, this date range should be filtered first before the calculation for @Latest Month.  So add the MONTH(Order Date) filter to context which will make this filter have a higher order of operation.


Finally, since we want to show only sales lines that meet minimum profit condition on latest month, drag @Profit Condition Filter to Filters pane, change to Exclude Values and select Null.  This will exclude sales lines for categories that don’t meet profit condition and hide those sales lines.




No comments:

Post a Comment