CODEWITHSUNDEEP
tableau-api
Travis
Travis

Reputation: 420

Datediff with Blended Data Sources

I have two date fields that come from two different data sources. One is from a CSV file of incident records, "Open Time", and one is from a SharePoint list "Go Live". The two data sources are blended on a string value they have in common called "DB Name".

Also the SP list "DB Name" field has a 1 to many relationship with the incident records in the CSV.

I need to find the difference in days between "Open Time" and "Go Live" for calculated column I want to use as a filter. Basically if the "Open Time" is 30 days or less after the "Go Live" date I want the incident record included.

I cannot figure out how to do that on a record by record basis with the aggregates Tableau seems to be forcing. Anyone have a code example for this?

I have to do it in Tableau 8.2, I cannot alter the data in either data source.

Thanks in advance for your help.

Upvotes: 0

Views: 1550

Answers (1)

Inox
Inox

Reputation: 2275

The best solution is to actually join those sources. But if that's not an option, there are some tricks with blending, but there are limitations

IF you have 1 to many relationship, you should use the "many" part as main datasource. This way, when you do 

DATEDIFF('day',MAX([Open Time]),MAX([SharePoint].[Go Live]))

the relationship will be many to one, and not one to many, and the MAX() will do nothing.

The limitation is that this only works when you explicit the [Open time] on the worksheet, otherwise it will take only the MAX value.

Upvotes: 0

Related Questions