CODEWITHSUNDEEP
chartsdatasettableau-api
Chris
Chris

Reputation: 5804

How to merge two data sets in Tableau via relationship?

I'd like to create a combination bar/line chart and combine two data sets. I'd like the columns to be months and the rows to be the aggregate values within those months. However, I'd like one of the data sets to display as a bar chart and the other data set to display as a line chart. It should look like this:

dataset 1 would be the bars and dataset 2 would be the lines

I have the following data sets: 

data_set1 = [{'value':10,'date':2016-01-01},{'value':15,'date':2016-01-02},{'value':5,'date':2016-01-03},{'value':10,'date':2016-01-04},{'value':120,'date':2016-02-01}]

data_set2 = [{'value':100,'date':2016-01-01},{'value':200,'date':2016-02-01}]

I've been able to create the bar chart (see below): This chart shows dataset 1 by month

However when I try to add the values from the second data set by also adding it to the columns section, I get the following error:

"In order to use fields from dataset 2, a relationship needs to be created with dataset 1. Select Data > Edit Relationships to open the Relationships dialog box."

I tried to create a relationship between these two data sets by the following process:

  1. Set the Primary Data Source as data_set2, since that is the smaller dataset.
  2. Selected Custom to create a custom relationship
  3. Selected Add and set both the 'Primary Data Source Field' and the 'Secondary Data Source Field' as Date

However,

Upvotes: 0

Views: 1504

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11896

When defining the relationship between two date type fields, try selecting the level of aggregation for the date fields, such as Month(Close_Date) in source 1 maps to Month(Date) in source 2. In effect, every value in those fields is first truncated to the beginning of the period (month in this case) before comparisons are made.

Upvotes: 1

Related Questions