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

# Segmentation Help

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.

• ###### 1. Re: Segmentation Help

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

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]

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

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

Steve,

You are 1 minute faster.

Shin

• ###### 5. Re: Segmentation Help

And Ivan, you are only 1 minute  late..

Thanks,

Shin

• ###### 6. Re: Segmentation Help

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

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