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

    2 Step Aggregation

    Ryan McClure

      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)
      10012015101
      10022015102
      10032015102
      100120151110
      10022015112
      10032015119
      10012015127
      10022015128
      10032015120

       

      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
      2015100
      2015112
      2015121

       

      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