5 Replies Latest reply on Feb 10, 2016 12:56 PM by Robert Priewe

    Hide new customers on 2nd period

    Robert Priewe

      Hello everyone,

      I'm pretty new to Tableau and I ran into a problem which is probably pretty easy to solve. I've Googled for a while now but couldn't find anything, probably because my search terms aren't accurate.

      I'm trying to display customer data over 2 periods and want to create a calculated field to display a flag if this is a new customer so i can filter it etc. Basically this is what my data looks like, each period has 1 entry:

       

      Customer #Period
      Balance
      1Jan50
      1Feb40
      2Feb60
      3Jan35
      3Feb30
      4Jan40
      4Feb38

       

      As you can see, customer #2 didnt exist in January so I want to create a calculated field that tracks that. For example call the field new customer and then if it doesnt have an entry in period 1 just set that to Y

       

      Thanks for the help,

      Robert

        • 1. Re: Hide new customers on 2nd period
          Charlie Archer

          Hi Robert,

           

          Are your months formatted as you have them in the data table?

           

          If so, my recommendation would be to create a calculated field to create a number for each of your months like so:

           

          IF [Month] = "Jan" THEN 1

          ELSEIF [Month] = "Feb" THEN 2

          ELSEIF [Month] = "Mar" THEN 3

          ...

          ELSEIF [Month] = "Dec" THEN 12

          END

           

          Then i would create a second formula to create your lookup:

           

          IF {FIXED [Customer #]: MIN([Month No.])} = ATTR([Month No.]) THEN "First month" ELSE "Subsequent month" END

           

          I think this should work without having tested it but if not, give me a shout and i'll have another look.

           

          Thanks,
          Charlie

          1 of 1 people found this helpful
          • 2. Re: Hide new customers on 2nd period
            Steve Martin

            Hi Robert,

             

            I took a slightly different approach to Charlie that does not require either formatting or LOD expressions.

             

            Simply created a single calc and ran from there:

             

            [New this Period] If Window_Sum(Count([Customer #])) <=1 Then 'Y' Else 'N' End

             

            Convert to discrete, drag into position (in your case to the filters and select True) and then set the scope to Period assuming your table is the same layout as your example.

             

            Steve

            • 3. Re: Hide new customers on 2nd period
              MURALIDHAR VEERAM

              Hi Charlie

               

              I tried the above scenario and the second calculation is not getting executed.it says can't mix aggregate and non aggregate functions together.

               

              Please provide more insight on this as i am new to tableau.

               

              Many thanks!

              • 4. Re: Hide new customers on 2nd period
                Charlie Archer

                Hi Muralidhar,

                 

                I have to say that Steve's solution looks more simple than mine. I suspect that my calculation should be written as

                 

                {FIXED[Customer #]:MIN([Month No.])} =  [Month No.]

                 

                without including the ATTR function

                1 of 1 people found this helpful
                • 5. Re: Hide new customers on 2nd period
                  Robert Priewe

                  Thanks, this worked exactly how i wanted it (without the ATTR like you mentioned)