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:
CustomerMonth (YYYYMM)Sales ($k)1001201510110022015102100320151021001201511101002201511210032015119100120151271002201512810032015120
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 Threshold201510020151122015121
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
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.
