2 Replies Latest reply on Jan 5, 2017 3:01 PM by Yuriy Fal

    Create a crosstab on a single dimension and preserve the filters

    conor duke

      I have a table with a set of volumes and some binary indicator,

      The data is structured like

       

      feature 1, feature 2, feature 3, feature 4, feature 5,  Volume

           1               0               1               0          1               200

       

      And I use a calculation to establish the total amount of user with each feature

      feature_holding = [Volume] * [feature_1]

       

      I would like to create a cross-tab of the single dimension, kind of like cohort analysis, but slightly different.

       

      e.g.

                                  feature 1_holding, feature 2_holding, feature 3_holding, feature 4_holding, feature 5_holding,

      feature 1_holding,           X                         Y                            Z                              A                         B 

      feature 2_holding,           .                    .          .               .             .      

      feature 3_holding,

      feature 4_holding,

      feature 5_holding,

       

      Where each value is a integer which is a COUNT of the volume which has the combination of those features.

       

      I have tried to cross-tab the features, but get a table like below(which doesn't work)

      sSheet 38.png

       

      CAVEATS

      The table is very large SQL Sybase connection

      I have a number of other features which I need to filter on, but are not required for the cross-tab

        • 1. Re: Create a crosstab on a single dimension and preserve the filters
          Jeffrey Kritzman

          Hi Conor,

           

          It looks like you're running into issues with how data displays based on Measure vs Dimension and Continuous vs Discrete.  Try switching your variables on Columns between to be Discrete Dimensions.

           

          If this doesn't work, if you attach your workbook, we can better help.

           

          Jeff

          • 2. Re: Create a crosstab on a single dimension and preserve the filters
            Yuriy Fal

            Hi Conor,

             

            What you're trying to accomplish

            is a kind of analysis called

            Association Rules or Market Basket.

             

            In it simplistic form it is mainly about

            counting objects (baskets) containing

            any pair of items (features) from a set.

             

            The common approach -- and the one

            which is easy to analyse in Tableau --

            is to prepare a cross-matrix of features.

             

            For that one could shape the datasource

            into a special form -- a table of every item (feature)

            self-joined on a basket key (feature vector).

             

            As an example I've prepared such a datasource

            from the sample 32-row table (the same as yours)

            by UNIONing it 5 times (one part for each feature)

            then self-joining to a copy of that table (5-UNION)

            on a common key (f_vector) which is unique

            for every row in the original 32-rows dataset.

             

            Please find the attached wb and Excel datasource.

            Hope it could help to start.

             

            Yours,

            Yuri

             

            PS The alternative path (not requiring Tableau at all)

            is to use machine learning Association Rules algorithms.

            1 of 1 people found this helpful