7 Replies Latest reply on Feb 9, 2016 10:31 AM by Steve Mayer

    Segmentation Help

    brian shannon

      I am looking for some help to create dynamic segmentation groups based on the date filters selected.

       

      I attached sample data and a display of what the groups and data should look like.  I also included in the attached the SQL code I would write to accomplish the report.

       

      I needed to calculate the segmentation for the date period by player in a derived query.  Then I could group these players based on the segmentation they fell into.

       

      Any help/direction will be helpful.

        • 1. Re: Segmentation Help
          Steve Mayer

          You will need Tableau 9 for this solution, because it uses an LOD calculation to replicate your sub-select. Workbook is attached.

           

          Step 1: Create a calculated field for Trips. This is the distinct count of a derived string that concatenates [Meta Id] and [Gamingdate]:

           

          COUNTD(STR([Meta Id]) + STR([Gamingdate]))

           

          Step 2: Create a calculated field for Players. This is the just the distinct count of [Meta Id]:

           

          COUNTD([Meta Id])

           

          Step 3: Create an LOD calculated field that calculates Trips Per Twin at the [Meta Id] level of detail:

           

          { FIXED [Meta Id] : SUM([Twin]) / [Trips] }

           

          This is effectively doing what you'd normally do in a sub-select in SQL. Every record now has a dimension that calculates Twin / Trips at the Meta Id level of detail.

           

          Step 4: Create your segment based off of Step 3:

           

          IF [Twins Per Trip] <= 100 THEN

              "$0 - $100"

          ELSE

              IF [Twins Per Trip] <= 500 THEN

                  "$100 - $500"

              ELSE

                  "$500+"

              END

          END

           

          In the end, it looks like this in Tableau:

           

           

          Note that if you want the LOD calculation to only include Meta Ids in a certain data range, you'll have to update the LOD calculation accordingly. You'll want to read up on LOD calcs so you can see exactly what is going on. Overview: Level of Detail Expressions

           

          -Steve

          • 2. Re: Segmentation Help
            Shinichiro Murakami

            Brian,

             

            I don't quite understand your request  well, but here is something.

             

            [Meta ID Category]

            if {fixed [Meta Id]:sum([Twin])}>=500 

            then ">500"

            elseif {fixed [Meta Id]:sum([Twin])}>= 100

            then "100-500" else "0-100"

            END

             

            then put parameter of

            [Start Date] and [End Date]

             

            [Date Header]

            if [Gamingdate] >=[Start date] and [Gamingdate]<=[End Date] then [Gamingdate] end

             

            Put [Date header] in to Filter shelf.

             

            Change the filter to Context //  to make date filter affected in the formula.

             

            End result screen shot.

             

            Thanks,

            Shin

             

            9.0 attached

            • 3. Re: Segmentation Help
              Ivan Young

              If you have your data in a true db then Tableau allows you to write custom SQL.  I think your SQL statement still needs a little work. 

               

              Alternately you can build your segments with a Tableau calculated field named segment. It would like something like below

               

              IF SUM([Twin])/COUNTD([GamingDate]) >= 0 and  SUM([Twin])/COUNTD([GamingDate]) <= 100 THEN '0-100'

              ELSEIF SUM([Twin])/COUNTD([GamingDate]) > 100 and  SUM([Twin])/COUNTD([GamingDate]) <= 500 THEN '100-500'

              ELSE IF ................

              END

              • 4. Re: Segmentation Help
                Shinichiro Murakami

                Steve,

                 

                You are 1 minute faster. 

                 

                Shin

                • 5. Re: Segmentation Help
                  Shinichiro Murakami

                  And Ivan, you are only 1 minute  late.. 

                   

                  Thanks,

                  Shin

                  • 6. Re: Segmentation Help
                    brian shannon

                    Steve, thanks for the response.  I am new to Tableau and I read up on LOD’s.  They look powerful.  I was able to replicate your work in a new excel workbook.  But when I replicate it on my SQL database I get different results? The total players, trips and twin add up but the players are not being distributed into the right segments?

                     

                    Any thoughts or are there any little caveats I should be aware of?

                    • 7. Re: Segmentation Help
                      Steve Mayer

                      My only thought is that if you have additional filters on a worksheet using LODs, you might not get what you are expecting, because FIXED LOD calculations operate at the level of detail specified in the calc, which can sometimes include more records than the filter. Maybe you have a date filter?

                       

                      -Steve