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?
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):
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
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.
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,
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.
Mock_Dr_Volumes.xlsx 10.1 KB
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.
That is looking pretty good!! Thank you so much for your help!!!!