2 of 2 people found this helpful
You can join tables A and B together using a calculated field. Which version of tableau you are using?
I believe this option available in tableau desktop 10 version from long back.
While joining the tables select Create join calculation
You can join your tables as monthly aggregates? its bit confusing. To create Jan-2018 you have to write datetrunc calculation
Ex: Monthly aggregate calculation
Let me know with this steps, you can solve the problem or not?
2 of 2 people found this helpful
I have explained the same @ my video blog below Joins with Troubleshooting Tableau Part 2 - YouTube
Or you can read the blog below
A common mismatch scenario when working with date data occurs when the date values in one field of the join condition are captured at a different level of detail than the other field in the join condition. In this case you can use a calculation in the join condition to change the format of the field on one side of the join condition so that its format matches the other field in the join condition.
For example, suppose you have the following two tables of data:
Projector rental Patron DATE RESERVATION TYPE REQUESTER ID
Individual 233445589 1/28/2002 K-12 365948999 1/29/2002 Non-profit 233448888 12/5/2002 K-12 365948999 5/5/2003 Non-profit 334015476 3/12/2004
334015476 3/15/2006 City 211896980 7/8/2007 K-12 334015476 1/4/2008 Individual 560495523 3/8/2009 Non-profit 233445566 2/14/2014
233445566 12/21/2015 Non-profit 233445566 2/10/2016 Non-profit 233445566 ID FIRST NAME LAST NAME BRANCH MEMBER SINCE UNITS BORROWED FEES SUGGESTED LIMIT 454613981 Adam Davis West 2012 25 0 10 232502870 Alan
North 2000 1 0 15 298000916 Amanda Smith North 2001 54 6.4 15 233448978 Andrew Smith North 2000 36 3.50 15 233445566 Ashley Garcia South 2000 243 11.30 15 900005122 Brian Frank East 2011 12 .10 10 921491769 Elizabeth Johnson West 2010 19 .5 10 233445589 Fred Suzuki North 2000 52 .90 15 344556677 Henry Wilson South 2005 3 .2 15 939502870 Jane Johnson West 2017 0 0 10
To find out more information about new patron behavior, joining the Patron table to the Projector Rental table might provide some insight about which library services motivate new memberships. The common fields between the two tables appear to be "Date" and "Member since." However, the date values in each field are captured at different levels of detail. To join these tables on their respective date fields, use a combination of DATE functions in a calculation on each side of the join condition to make the level of detail in each field match.
Using the calculation in the join produces the following combined table:
DATE RESERVATION TYPE REQUESTER ID ID FIRST NAME LAST NAME BRANCH MEMBER SINCE UNITS BORROWED FEES SUGGESTED LIMIT
Individual 233445589 232502870 Alan Wang
2000 1 0.00 15 1/1/2000 Individual 233445589 233445589 Fred Suzuki North 2000 52 0.90 15 1/1/2000 Individual 233445589 233445566 Ashley Garcia South 2000 243 11.30 15 1/1/2000 Individual 233445589 233448978 Andrew Smith North 2000 36 3.50 15
To determine if a patron rented the projector in the same year he or she started his or her membership, add one more clause to the join based on ID.
The result of the additional join condition shows that only one patron might have started his membership to rent a projector.
DATE RESERVATION TYPE REQUESTER ID ID FIRST NAME LAST NAME BRANCH JOINED UNITS BORROWED FEES SUGGESTED LIMIT 1/1/2000 Individual 233445589 233445589 Fred Suzuki North 2000 52 0.90 15
Hope it helps !
Please mark the answer as CORRECT & HELPFUL if it really helps you so that it can help others as well
Thank you Ritesh and Akram! It was really helpful!