2 Replies Latest reply on Feb 26, 2016 6:27 PM by pooja.gandhi

# 2 Step Aggregation

Hey everyone,

I am a new Tableau user and first time poster (so apologies if this has been answered elsewhere).

I am currently trying  to figure out how many customers surpassed a sales threshold (call it \$5k) for the first time in any given month. For example, assume you have the following data:

 Customer Month (YYYYMM) Sales (\$k) 1001 201510 1 1002 201510 2 1003 201510 2 1001 201511 10 1002 201511 2 1003 201511 9 1001 201512 7 1002 201512 8 1003 201512 0

In Excel I would simply add a new column with the following formula in cell D2:

=IF(AND(C2>=5,COUNTIFS(A:A,A2,B:B,"<"&B2,C:C,">=5")=0),1,0)

then drag that formula down and sum by month across the new column to yield the desired result of

 Month # First Beating Threshold 201510 0 201511 2 201512 1

I can do this in SQL as well using a qualify statement, but I need the sales threshold to be dynamic for my end user without maintaining a connection.

Any suggestions here? I am at my wits end, so whatever help you can provide would be greatly appreciated.

Thanks,

Ryan

P.S. I am running Tableau Desktop 9.1.1 off of a .tde file in case that matters

• ###### 1. Re: 2 Step Aggregation

Hey Ryan,

Cohorts may be what you're looking for? Maybe take a look at this article: Tips for Cohort Analysis | Tableau Software

If that's not quite it creating a set may be better? http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.htm#sortgroup_sets.html

Hope that helps!

• ###### 2. Re: 2 Step Aggregation

Hey Ryan!

You can do this via LOD (available post Tableau version 9.0):

Calculation 1: IF [Actual Date ] = { fixed [Customer] : min(if [Sales (\$k)] >= 5 then [Actual Date ] end) } then [Customer] end

I converted your yyyyMM to actual date using dateparse:

Actual Date: dateparse('yyyyMM', str([Month (YYYYMM)]))

Place this actual date on columns and countd calculation 1 on text on the marks card:

Basically telling Tableau to throw a date on rows where sale is >= 5k and then at a fixed customer level give the min date of the result. If that min date = date then count that customer as the customer for the 1st time and since you are grouping these by months, it would give you a customer for the first time in that specific month.

Hope this helps..

Pooja.