1 2 3 Previous Next 31 Replies Latest reply on Apr 27, 2015 11:18 AM by Jared Raymond

# How do I classify groups based on date calculations from converted/parsed dates?

I watched the Tableau training video on date calculations and reviewed the forum questions on date calculations/conversions but I'm still having trouble.

I want to classify data into 2 groups. Let's say "E" and "A". The classification into these groups is based on the difference between Date 1 and Date 2. By definition, if date 2 is the same year as date 1, then it's group E, otherwise it's group A. (Date 2 can't be before Date 1 by definition)

The problem is that date 1 is yyyy and date 2 is "mm-dd-yyyy 12:00:00 AM" I want to say that if date 1 and date 2 (just the yyyy) are equal, then it's group E, if not, then group A.

I tried parsing the dates since they were originally strings.

Date 1 Field: (DATEPARSE("yyyy", [field name 1]))   (field name edited)

Date 2 Field: DATEPARSE('yyyy', RIGHT([field name 2],4) )    (I was hoping that the 12:00:00 AM wouldn't matter and that the right four would just count the yyyy)

Then I created the two groups

A: If DATEDIFF('year', [Date 1 Field],[Date 2 Field]) = 0 THEN 0 ELSE 1 END

E: If DATEDIFF('year', [Date 1 Field,[Date 2 Field]) = 0 THEN 1 ELSE 0 END

This should classify each applicable instance as an E or an A, and I wanted to count the E's and A's, but this seems to classify everyone as an A regardless of the information.

Any ideas? I'm probably over thinking this.

• ###### 1. Re: How do I classify groups based on date calculations from converted/parsed dates?

It may be that you've made this more complicated than necessary. The community can help you better if you provide a small sample of the data (even if you have to anonymize or dummy it up).

So if [Date 1] is coming in as a String (not Integer?) containing a Year, e.g. "2014," then one way to force that to convert to an actual date is to prepend it with a string with some day chosen. So if for example you pretend that all events in 2014 for [Date 1] occured on New Year's Day, then you can get by making a Calculated Field called [Date Field 1] like

DATE(' 1/1' + [Field Name 1])

And if [Field Name 1] had come in as an Integer, you can either Change Data Type to String, or wrap it with the STR function in the Calculation above.  Either way, that would make it  real actual date.

Based on your post, [Date 2] seems to be converted more or less correctly. You are right that the time-of-day portion will not matter for purposes of this exercise  (nor will the day.)

--

Now as far as classifying A vs E and then counting them --   does your source data have a unique identifier per Row? If so, this gets easy.

Instead of 0's and 1's , why not just establish the A and E directly since that's what you were after? Make a Calculated Field (maybe called "Class" ? ) that goes something like

IF YEAR([Date Field 1]) = YEAR ([Date Field 2]) THEN 'E' ELSE 'A' END

And then assuming you have some sort of ID or Key that identifies each Record uniquely, then you can do a "A Flag" that goes  IIF([Class]='A',[Record ID],NULL)   and an "E Flag" that goes IIF([Class]='E'',[Record ID],NULL)

If you COUNTD either the A Flag or E Flag, you'll have sums that persist no matter what Filters are applied later.

There are of course other ways to do it.

• ###### 2. Re: How do I classify groups based on date calculations from converted/parsed dates?

I am not sure why, but every attempt I make (I'm at about 4-5 different ways) tends to classify all of the items as one group and none in the other. I'm new to Tableau so I'm having difficulty figuring out how to view the actual records and determining why Tableau is classifying them all in one category. I feel like I'm playing Battleship and I'm just getting "hit" or "miss"

• ###### 3. Re: How do I classify groups based on date calculations from converted/parsed dates?

That's why the best thing you can do to get help is to Attach a .twbx that contains a small sample of your data. If you need to anonymize it or make "dummy" entries that's fine so long as the field names and formatting are faithful to your real data.

It's also helpful for troubleshooting if you make yourself simple, temporary vizzes, one step at a time.  So for example make a new Worksheet, and lay up [Field Name 1] , [Date Field 1],  [Date Field 2] on the Row Shelf. What do you see? Do the results make sense?

• ###### 4. Re: How do I classify groups based on date calculations from converted/parsed dates?

Also, while I will worry about this once I can get any type of calculation to work on classifying the fields, the converting date 1 to 1/1 + Year may not work as the month may matter down the road (ie dates past a certain month may be classified in one year in dates prior to that will be in another) But I'm just happy if I can get one step closer and get anything to work.

• ###### 5. Re: How do I classify groups based on date calculations from converted/parsed dates?

Well Tableau can't and shouldn't be expected to produce phantom data out of thin air, so if the month matters, then your Data Source would need to carry month in it.  Until it does, if your Data Source only gives Year, then that's what you have.

• ###### 6. Re: How do I classify groups based on date calculations from converted/parsed dates?

My apologies. I misspoke. I meant the month from date 2 may matter.

• ###### 7. Re: How do I classify groups based on date calculations from converted/parsed dates?

Yes, and we were under the impression that you have that. It sounded like it was comparison of the Year across the two date fields that was not working to your liking.

• ###### 8. Re: How do I classify groups based on date calculations from converted/parsed dates?

I will ask for approval to post sample data as I agree that it is the best way to receive assistance should the discussion of the concepts behind the query prove difficult.

• ###### 9. Re: How do I classify groups based on date calculations from converted/parsed dates?

Just use Excel and make up 10 rows of data with dummy values, but with fields named and formatted the same way as your source. That way you don't need permission or risk divulging any company proprietary information.

• ###### 10. Re: How do I classify groups based on date calculations from converted/parsed dates?

I was able to look into the data by right clicking on a sheet, selecting "View Data" and then selecting the underlying tab.

So a few of the records had Date 2 as "6/30/2014 12:00:00 AM" "8/7/2014 12:00:00 AM" and "8/25/2014 12:00:00AM" but the DateParse for that field reads "12/30/1899 12:00:00 AM" for all of them. And the Date Parse for the first date appears to be null across the board.

To refresh, here are my current calculations.

Date 1: DATETIME('1/1' + [Field Name])  (I made it date time to keep both fields as dates rather than 1 integer 1 date)

Date 2: DATEPARSE('yyyy', RIGHT([Field Name],4) )

• ###### 11. Re: How do I classify groups based on date calculations from converted/parsed dates?

I switched Date 1 to:

DATEPARSE('yyyy',[Field 1])

and now it turned from nulls to "1/1/2012 12:00:00 AM"

and since my A vs. E calculation uses just the YEAR function, I think that that part should be fine.

• ###### 12. Re: How do I classify groups based on date calculations from converted/parsed dates?

Probably. If you need any more help, please provide a sample.

Good luck.

• ###### 13. Re: How do I classify groups based on date calculations from converted/parsed dates?

I made a quick set of columns trying to match what I've described. I don't know how to copy the more complicated nuances of the data without actually copying the data, so this should match what I've been trying to explain.

I'll try to explain what I'm looking for and what I've done again.

So there are 3 categories. One is those that don't have a second date, nothing needs to happen with them. Another is those that have a second date, but it's in the same year as the first date. (They get an E) The third is those that have a second date, but it's in a later year than the first date. (They get an A)

So I created two dimensions: Field 1 and Field 2 are Dates 1 and 2 from the spreadsheet respectively.

Dimension 1: DATE(DATEPARSE('yyyy',[Field 1]))

Dimension 2: DATE(DATEPARSE('yyyy', RIGHT([Field 2],4)))

Then I created two measures:

E: If YEAR([Dimension1])= YEAR([Dimension2]) THEN 1 Else 0 END

A: If YEAR([Dimension1])= YEAR([Dimension2]) THEN 0 Else 1 END

Then I made a table in Tableau with Sum(E) and Sum(A) as Marks and all of the instances show as A's. The reason appears to be that Tableau considers Dimension 2 to be "12/30/1899" no matter what Date 2 is (for the non null values) The other problem is that I think Dimension 2 counts all of the Null values for Date 2 and factors them in so that they are A's because they won't equal Dimension1/Date1 because that is not null.

• ###### 14. Re: How do I classify groups based on date calculations from converted/parsed dates?

So in your sample, Tableau already sees [Date 2] as a Date, so there's no additional calculation needed there. And [Date 1] comes is an Integer and thus a Measure, which is also what should be expected.

If you wrap a DATE or DATEPARSE function around a field that is already a Date, then you will definitely get a bogus result  (the "1899" result you saw.)

You probably have other fields in your data set that you're not showing, so all we have for unique identification currently is [Person] so ultimately it's the COUNTD of [Person] that will sum up the A's and E's when you later get into filtering and such.

There are several other routes one might take, but it certainly seems like you've been making this exercise hard on yourself.

See if the Attached helps.

1 2 3 Previous Next