5 Replies Latest reply on Jun 17, 2014 8:29 AM by Keshia Rose

    Trying to Use One Date Column for Two Datasources

    Jeff McDonald

      Hi all,

       

      Attached is a workbook I'm trying to create. There are two data sources, Albright Appts which has all the showings for this specific house for sale. And Albright System Notes which has all the changes our database has made to that record. For simplicity's sake I've removed everything but Current List Price from the datasource itself. So what we're seeing in System Notes are price changes to the house and the date the price change occurred (basically, New Value = Current List Price).

       

      I want a graph that shows Month/Year along the bottom axis, with a line marking the current listing price with the current listing price being the left axis. Then a line with the number of appointments for the house and that count as the right axis. The problem I'm having is that the best way I've found to get this to even work a little is to add the two data sources into Tableau separate and then link them on the date fields (which are date/time fields out of the database, but in Tableau I converted them to just date fields). But as you can see by the attached workbook it only shows appointments when an appointment occurs on the same day as a price change. So how can I create a "neutral" month/year bottom axis and then have the 2 graph lines I want showing correct information for that month/year for either Current Listing Price or Number of Showings?

       

      Thanks,

       

      Jeff

        • 1. Re: Trying to Use One Date Column for Two Datasources
          Michel Caissie

          Jeff,

           

          If you want the blending of the two datasources to occur  at the  Month - Year level,    create on both datasource 2 new fields  MONTH([Date])  and  YEAR([Date]),  and    MONTH([Incident Date]) and YEAR([Incident Date]).

          Convert those two calculated field as a Dimension  and go in Data - Edit Relationship  and add a custom relation ship between them.

           

          Next remove the blending on Incident Date and apply it on  Incident Date (Month) and Incident Date (Year) (The little orange chain symbol)

           

          Finally, right-click on the SUM(Number of Records) axis , select Format ,  Pane - Special Values section ,  and set Marks at Hide(Connect lines)

           

          Michel

          • 2. Re: Trying to Use One Date Column for Two Datasources
            Jeff McDonald

            Very nice, thanks! For some reason it's not showing all the points on the SUM(Number of Records) line though. I set it to always Mark Labels to see things easier. And the numbers that are there are correct. 3 for March 2012, 2 for July 2013, 5 for Sept. 2013, etc. But if you look at the underlying Albright Appts data there should be data points on just about every month instead of the gap from March 2012-July 2013, as well as the other smaller gaps.

             

            And as a quick aside, why are the dates a month off? Take, on the axis, Feb. 2012. When i hover over the datapoint right above it it tells me that it's actually for March 2012.

             

            Thanks again for your help here! It's much appreciated!

             

            Jeff

            • 3. Re: Trying to Use One Date Column for Two Datasources
              Keshia Rose

              There is probably a much better answer to this but here is my solution:

               

              1. Create a new data source with all possible dates found in Albright Appts and Albright System Notes.

              2. Connect to that data source and start a new worksheet in your workbook.

              3. Set up your relationships with the new data source for date and initial date etc.

              4. Make sure the date in the new data source is a date and not datetime

              5. Drag date from the new source to columns and continue to build the view

               

              That's my method (attached a workbook), again, probably not the best method but it works.

               

              EDIT: Also, the months aren't off, if you turn on tick marks you will be able to see it better. The label position can be a bit misleading.

              • 4. Re: Trying to Use One Date Column for Two Datasources
                Michel Caissie

                Jeff,

                 

                What happens is  because in this view, you selected  System Notes as the Primary, a mark is placed for all Dates in Notes and if  an Incident Date exist for that Date then it is displayed.

                 

                But if you want to display all incident Date  and Mark the corresponding Date , just use  appts as the Primary.

                See in the attached

                 

                Or you can use Keshia solution if the blending is only on Date fields. It is not complicated to create a calendar datasource in Excel.

                 

                Michel

                • 5. Re: Trying to Use One Date Column for Two Datasources
                  Keshia Rose

                  Jeff, if all of the dates in Albright System Notes were found in Albright Appts then Michel's solution would be fine and you would not need to make another data source. However, if you look at his solution, you are missing any dates in Albright System Notes that are not found in Albright Appts. For example, where is the value for December 1st, 2012 of 179,900?