ABC analysis

Version 1

    ABC analysis in Tableau typically involves Table Calculations --

    as the logic behind grouping items is based on their Percentile Ranks.

     

    For example, the 'A' group would consist of the first 20% of Products

    ranked by their Sum of Sales, 'B' would be the next 30%, 'C' -- the rest 50%.

     

    Here is a nice example of an ABC analysis (courtesy of Keshia Rose):

     

    Re: How to create something like group based on table calculations

     

    Though easy to get -- using a RANK_PERCENTILE() Table Calculation function,

    or even a simpler equivalent calculation, something like  INDEX() / SIZE() --

    these ABC group 'values' are not easy to re-use in subsequent analyses.

    That's because the ABC group values are not belonging to any Dimension.

     

    Making them a Dimension typically requires manual tasks -- such as

    'exporting' the results (into an external table) then importing (joining) 

    into the target datasource(s) for subsequent analyses.

     

     

    1) This workbook is reproducing the Table Calculation based logic by using

    FIXED LOD expression(s) -- with the PERCENTILE() Aggregate function

    instead of the RANK_PERCENTILE() Table Calculation one.

    The result is an 'ABC' Dimension, which could be re-used just in place.

     

    Besides, a slight modification of the granularity of the 'basic' FIXED LOD expression

    would allow for further segmentation, such as making ABC groups within each Category:

    Screenshot 2019-08-17 at 20.17.22.png

     

     

    2) There is a special variation of a 'classic' ABC, i called it (blatantly) a Pareto-ABC.

     

    The logic is the same as in a Pareto analysis, such as to find the Products that constitute (cumulatively)

    the 75% of the Total Sales and label them as an 'A' group, the next 15% (up to 90% of the Total Sales)

    would be labeled as a 'B' group, and the rest belonging to the 'C' one.

     

    Again, getting to the result ('labelling' Marks on a view with ABC 'codes') is relatively easy with Table Calcs.

    But turning them into a Dimension column (right in the datasource) is impossible.

    The Tableau's Order of Operations does rule this out.

     

    And again, there is a workaround (a rather complicated one) --

    namely, an 'old-school SQL' approach with a (logical) non-equi Join.

     

    The Pareto-ABC datasource has been made initially as a Cross-Join --

    namely, a Self-Join of the Orders table (ON 1=1 Join Calculation condition) --

    to benefit from the Multi-Table Extract (hence version 2018.3+ of the workbook).

     

    The actual logic of a non-equi Join is implemented using the (datasource) Filter.

     

    As a result, a row from the 'left' (Orders table) would be joined to any row on the 'right'

    which fulfil the True condition in the Filter, thus the Products from the 'left' table

    would be joined with the ones from the 'right' which have less (or equal) Sales.

     

    The combination of the Filter and the FIXED LOD expression(s) would be considered

    a logical equivalent of a RUNNING_SUM() Table Calculation -- or a RUNNING Window Function

    (in the case of a Live Connection to any SQL-2003 compliant DBMS).

    Again, it's a simple Aggregate calculation inside a FIXED LOD expression,

    so one could do any 'Pareto-ABC'-like analyses using the Pareto-ABC Dimension.

     

     

    !!! A caveat: the performance of the latter workaround would depend greatly

    on the size and granularity of the 'original' datasource (the Orders table in the workbook).

    Even with the small number of Products (1850) and the small size of the data (<10K rows),

    the non-equi Join + FIXED LOD combo would take a substantial resource consumption --

    mainly because of all the hard work made by HyPer at the runtime (both Joins and FIXED LODs).

     

    Another nasty surprise is that the workbook couldn't be saved to the Tableau Public :-()

     

    This is the error when trying to save to Tableau Public:

    Screenshot 2019-08-17 at 23.11.02.png

     

    It looks like the 'tricky' Multi-Table extract would bring Tableau Public into an unusual state of mind ;-)

     

    So i guess the TWL to be the place for the workbook like this. Please find the attached.

    Use with caution (especially the Pareto-ABC stuff). Have fun!

     

    Yours,

    Yuri