How best to handle data with multiple date fields?

We will be supporting some basic date arithmetic, namely to provide trailing period reports (ie, last 3 days, week, month quarter, year, etc).

However, we realize that this is still lacking. Creating metrics based on date arithmetic is a feature we are working on implementing. Currently, there are some lengthy workarounds that we hope to change soon.At this point, I can't give an exact date as to when this feature will be implemented, but we are working to making it happen!

Thanks for your patience in this matter, and if you have any other questions, please feel free to let us know.

Just an update here... today we released an update to the application that now allows mapping multiple dates.

The trick to this is to perform the mapping from the data management portion of the application (Data tab) and mapping each date to a date field. The application will then make new date hierarchies for each date (ie, Start Date or End Date >> Start Date Month, Start Date Year, etc).

Additional note regarding date labels in the application:

"Date" = yyyy-mm-dd
"Date US" = mm/dd/yyyy

(we will update this to make it clear!)

Hi Tom,

I'm sorry you are having problems with this. We know this is tricky and are working to greatly improve this. In the meantime, here are some steps that I hope will help you achieve what you are trying to do.

After uploading a file, your unmapped date columns should be presented as any old attribute. Go to the data management section (Data tab) and select the attribute that you would like to map to a date dimension. From the attribute's properties page, click the "Map Attribute" presented in the right column.

This should bring up the map attribute dialog box. In the first column, select the "Provided Attributes" folder. In the second column, select the "Date" attribute.

Now comes the tricky part and what may be causing errors. After selecting "Date", note the drop down box asking to select a label. There are two choices here:

1) Date - This refers to dates formatted as YYYY-MM-DD (ie,, 2009-08-12)

2) US Date - This refers to dates formatted as MM-DD-YYYY (ie, 08/12/2009 - note the preceding "0" in the month, which is required)



Here it is required to select the appropriate date format which corresponds to the way your date is formatted in your data. At this time, only these two formats are accepted.

By mapping in this manner, multiple data mapping actually create multiple Time dimensions. So a Start Date will create a time hierarchy called "[Start Date] Month, [Start Date] Day," and so on. A subsequent End Date will create another hierarchy labelled as "[End Date] Month, [End Date] Day." So in one file, while it is not possible to map multiple columns to "Start Date", it is possible to designate multiple date fields.

I know this is confusing, but I hope this helps you achieve what you are trying to do. If you continue to have problems, I'd be happy to set up a call with you so we can try to help. Just let me know.

Thanks,
Ray

support email: support@gooddata.com
support phone: (415) 200-0194

Hi Tom,

I'm sorry you are having so many troubles with this. This error message is saying that there is at least one source value that cannot be mapped to the date dimension.

The only thing I can think of is that there is a misformed date in the data, or possibly a null value. I'm guessing in this case that it's the latter. We have seen this before and are working on ways to address a null value when mapping.

Can you tell me if you have any null values in the date data?

Thanks,
Ray

Hi Tom,

There is no simple workaround for this that I can come up with, other than giving it all null values a fake date. I am sure you don't want to do this, so I am looking into updating the application to allow for null values when date mapping.

I believe this can be done rather simply, but requires changes to the production platform on our side. I am talking to guys here about this, and I hope we can have this updated asap. I will keep you posted.

Thanks,
Ray