7 Replies Latest reply on Jan 2, 2013 8:45 AM by annette.hatch

    Flagging new Customers

    annette.hatch

      I am trying to figure out how to flag new doctors based on the first time one of their patients was admitted.  I tried to use the following expression, but it is not working for me.

       

      "If((DATEDIFF ('month',(TODAY()),('month',([admit_date_min])<6))) then 'new' end" 

       

      Any guidance you can give would be greatly appreciated. 

        • 1. Re: Flagging new Customers
          Shawn Wallwork

          Annette, are you looking for this:

           

          If DATEDIFF('month',[admit_date_min],TODAY())<6 then 'new' end

           

          This tests to see if the admit date is less than 6 months. Is that what you're looking for?

           

          --Shawn

          • 2. Re: Flagging new Customers
            Joshua Milligan

            Annette,

             

            You might check whether this part of the statement is giving you a positive or negative number (you can create a new calculated field with just that part to check):

             

            DATEDIFF ('month',(TODAY()),('month',([admit_date_min])<6))

             

            I'm guessing you have the last two arguments switched and want something like this:

            IF DATEDIFF('month', [admit_date_min], today()) < 6 THEN "new" ELSE "not new" END

             

            Joshua

            • 3. Re: Flagging new Customers
              annette.hatch

              Unfortunately, both of those corrected calculation issue, but it did not produce the desired response. 

              I do volume reports for a hospital.  We base our volumes on the total number of records.  For each record, there should be an admit date and an admitting MD.  Each doctor is listed by last name and their totals for the previous 3 years by year and month are listed.  I would like to see a flag "new" whenever a new doctor id is linked to a new patient.  The last calculation tagged every doctor with volumes as 'new'.

              I thought the "date_min" would only tag the first date. 

              • 4. Re: Flagging new Customers
                Shawn Wallwork

                Annette, stick 10 rows of bogus data into an Excel file and post it. That works better than trying to guess how to mock up your data for you. If you'll do that bit of work, I'm sure we can sort out the calculation for you. Thanks,

                 

                --Shawn

                • 5. Re: Flagging new Customers
                  annette.hatch

                  First please let me say, "Thank you, thank you, thank you."

                   

                  Here is some sample data:

                  The Admitting_Id is a unique identifier for each Doctor

                  The columns would be the sum of the number of records for that month/year with the grand total at the end.

                  There are a number of months were doctors do not admit patients.  I am hoping not to count each doctor as new because in the previous month he/she had zero volumes. 

                   

                  In the end as an example, I would like a label Dr. Payne's volumes for October to say "new".

                  The other solution would be the ability to identify those doctors whose first entry is in the last 6 months and create a filter to list them.

                  • 6. Re: Flagging new Customers
                    Shawn Wallwork

                    Happy New Year Annette!

                     

                    I'm still a little hazy on exactly what you're after (no not from New Year's cheer). But I think this should get you going in the right direction. There are a few things you're going to want to understand a bit better about Tableau. First T wants (prefers) a single row of column headers, and second T wants 'real' dates. So if you really have your data setup the way you did the mock up, then you'll want to combine these together into real dates like: 1/1/2011, 2/1/2011, etc. So that's the first thing I did.

                     

                    Next, anytime you have dates running across the table like that it is best to 'reshape' the data, which turns out to be easy if your data is in Excel because of Andy's add-in reshaper tool. Essentially it takes your wide table and transforms it into a tall table. In the attached Excel workbook you can see the difference between the two sheets.

                     

                    Now that the data is transformed it's just a matter of setting up the pills correctly. To get the MIN(Date) for each doctor all you need is a discrete (blue) date field set in MIN(). Then I used a parameter-drive field calculation to generate the New/Old labels (similar to what Joshua and I had suggested previously). Using the parameter allows you to change the break point as needed.

                     

                    Hope this helps.

                     

                    --Shawn

                    • 7. Re: Flagging new Customers
                      annette.hatch

                      That is looking pretty good!! Thank you so much for your help!!!!