10 Replies Latest reply on Aug 22, 2014 1:40 PM by Harry Standley

    Linking dates from calendat table to other tables

    Harry Standley

      Folks,

       

      I have a main calendar table and a sales table and others. When I look at the relationship Tableau makes the correct join however, I do not see the relation link between the two in the workbook. It does not let me select my year and week from that calendar to show sales for that period, only when I pick the year and week from the sales table. I have other tables that I will be using so I need to be able to use my calendar table and tie all the data to that year and week. For instance my rebate table, I want to see all sales and rebates for a period selected using the calendar date. The data types are the same, string. I am using 8.1 and using custom qrys.

       

      Thanks,

      Harry

        • 1. Re: Linking dates from calendat table to other tables
          Harry Standley

          Sorry, I am using 8.2.

           

          Harry

          • 2. Re: Linking dates from calendat table to other tables
            Joe Oppelt

            Give more detail.  Better yet, provide a packaged workbood to demonstrate what you want (or to demonstrate how far you've gotten so far.)

             

            From your description I'm guessing that you have a calendar sheet somewhere, and your user clicks on a date, and you then want to show pertinent data for that date.

             

            I have an extensive workbook that does precisely this.  I use filter actions to take the selected date, and display in all sorts of other sheets the pertinent data for that date.

            • 3. Re: Linking dates from calendat table to other tables
              Harry Standley

              Joe,

               

              I am still new to this, remind me one more time how to send a packaged workbook.

               

              You assumption is correct I have a master calendar. How do I get to the filter actions?

               

              Harry

              • 4. Re: Linking dates from calendat table to other tables
                Joe Oppelt

                In Tableau desktop, choose FILE => Export Packaged Workbook

                 

                That will create a .TWBX file that will contain your current workbook and your data.  They others can walk along with you using your own stuff to discuss your situation.

                 

                Now, you may say, "Hey, I don't want to post proprietary screens and data on a public website."  That's a common concern.  And I have that precise concern when I post a workbook with my questions.  In those situations, this is what I do:

                 

                 

                0)  Save your workbook in case you need to recover from messing up the following steps.

                 

                1)  click VIEW DATA for your data source.  Cut-and-paste it all into an excel sheet.

                 

                2)  Mess with the data in excel.  (Just keep a dozen records or so.  Just enough to demonstrate the issue.  Change customer names to Disney characters.  Change other proprietary data as well.  Delete columns that were created from your existing calculated fields.  (There is a good reason for this!  You want your existing calc fields to stay calc fields and not become data source fields when you re-connect.)  Save your excel.

                 

                3)  In tableau Do CONNECT TO DATA.  Connect to your new excel sheet.  Now you have a second data source identical to your original.

                 

                4)  Do REPLACE DATA SOURCE, and swap your original source for the excel source.

                 

                5)  Close the original source.

                 

                All your existing stuff should work identically as it did when you had your actual data source, except now you have bogus data.  (And if you don't like what you see on your screen with your new data, you have liberty to edit excel as you see fit.)  (Note:  If it does not work properly, either tweak the excel sheet and try again, or hit the BACK arrow a bunch of times to undo steps.)

                 

                6)  Save your workbook to some TEST_NAME name.

                 

                7)  Delete all the other sheets and dashboards you don't need to demonstrate the issue.

                 

                8)  Then do FILE => EXPORT PACKAGED WORKBOOK.

                 

                Upload it here.

                • 5. Re: Linking dates from calendat table to other tables
                  Harry Standley

                  Joe,

                   

                  I found the actions in filter and tried to set it up however, the target data source is grayed out and will not allow me to change it. Also can I use multiple target data sources on one action?

                  • 6. Re: Linking dates from calendat table to other tables
                    Joe Oppelt

                    I find it best to target sheets, not data sources directly.  I have filter actions that go from my calendar to dashboards with multiple sheets that are based on different data sources.  The actions pull the filter data from the calendar and "distribute" it to each sheet, even though they are based on multiple data sources.

                     

                    Let's get a TWBX and I can discuss your specifics.

                    • 7. Re: Linking dates from calendat table to other tables
                      Harry Standley

                      Joe,

                       

                      Working on it now.

                       

                      Thanks,

                      Harry Standley | Business Systems Analyst |  <http://www.georgesinc.com/>  | PO Drawer G | Springdale AR 72765-2030

                      479.927.7127 | Help Desk: 479.927.7777 | harry.standley@georgesinc.com<mailto:harry.standley@georgesinc.com>

                       

                      “Excellent coaches are interested in the success of the individual, not in making mini-me’s anywhere. Therefore, mentoring is about censoring, and coaching is about allowing. Are you mentoring or coaching?”

                       

                      Tipton, Robert S. (2010-04-26). Jump! (p. 197). ALDEN-SWAIN PRESS. Kindle Edition.

                      • 8. Re: Linking dates from calendat table to other tables
                        Harry Standley

                        OK Joe,

                         

                        Here is my example and by the way thanks for showing me how to set up my data correctly before showing it to the world.

                         

                        Again I have a calendar with year and week and in my sales table I have year and week as well. I will eventually add a few other tables that also will contain year and week fields. My product table work well when I link it together as well as my other table not show here. For some reason it is the calendar link that is not working.

                         

                        Thanks,

                        Harry Standley | Business Systems Analyst |  <http://www.georgesinc.com/>  | PO Drawer G | Springdale AR 72765-2030

                        479.927.7127 | Help Desk: 479.927.7777 | harry.standley@georgesinc.com<mailto:harry.standley@georgesinc.com>

                         

                        “Excellent coaches are interested in the success of the individual, not in making mini-me’s anywhere. Therefore, mentoring is about censoring, and coaching is about allowing. Are you mentoring or coaching?”

                         

                        Tipton, Robert S. (2010-04-26). Jump! (p. 197). ALDEN-SWAIN PRESS. Kindle Edition.

                        • 9. Re: Linking dates from calendat table to other tables
                          Joe Oppelt

                          I made a very rudimentary sheet called CALENDAR.  it's just weeks and years.  I threw that on DASHBOARD1.

                           

                          I made a very basic SALES sheet.  It just shows CASES for the week/year selected on DASHBOARD1.  I threw that on DASHBOARD2.

                           

                          (I also created a Data Relationship btween the Calendar Data Source and the Sales Data Source, though in this case I really didn't need it.  But you would need it if you wanted to do a parent/child join of the two sources on one sheet, for esxample.)

                           

                          I see you have CASES data for week 48 in years 2014 and 2013.  So go to DASHBOARD1 and hover over week 48 for 2013 or 2014.

                           

                          You'll get the tooltip for that block.  You'll see what looks like a hyperlink that says Filter1.  That's the fliter.  Click it.

                           

                          You'll go to DASHBAORD2, and you'll see CASES only for that date period.  (Hover over the blue bar and the tooltip will tell you the week, year and number of cases.)

                           

                          The Action Filter is a DASHBOARD Action.  To edit it, go to the top menu on Tableay Desktop, and under Dashboard select ACTIONS.

                           

                          You'll get the Dashboard Action editor.  Click Filter1 and hit the EDIT button.


                          There is a SORCE and a TARGET box.  It defined where you expect to go from, and where you want to go to.  I selected by Dashboard, and then selected the specific sheet.  (In this simple example there is a 1:1 relation between the two, but you could have a half dozen sheets on a dashboard, but you might just want an action to work from only one or two of the 6.  This is where you want to specify that.)  Same principle applies to the TARGET section.  (This is where (in my own application) I specified the filter value going to multiple sheets with varying data sources.)

                           

                          In the bottom section I hit the SELECTED FIELDS button.  Then I hit the ADD FILTER button at the bottom.  This is where I tell Tableau what field(s) to use as the filter from the SOURCE sheet, and where to apply them in the TARGET sheet.  In this example it's straightforward.  Fields are identically named.  There is only one target data source.  But if you had two data sources, you would have to create two sets ot filter entries.  (One field at a time.)  Suppose there was a data-source-3, and the WEEK field in that was was called PURCHASE_WEEK.  You would have to tell Tableau to apply WEEK from CALENDAR to PURCHASE_WEEK from data-source-3, for example.

                           

                          Near the top right corner of the Action edit box are three buttons:  Hover, Select, Menu.  These tell Tableau how to "launch" the filter.  I chose MENU.  That's why you see "Filter1" in the Tooltips box.  You can actually have a ton of different choices for a user to do on a calendar mark, for example.  (I do, in my application.  They can go to a dashboard to show income, another to show expenses, another to see maps, etc.  So in the user's tooltip they see various links.  You change the name of the Action at the very top of the Action edit box.)  You could also choose HOVER, and as soon as a user hovers over a mark, the action engages and off he goes!  (That's tough on the user if he's not adept with a mouse!)  And SELECT means it activates when he clicks on a mark.

                           

                          Anyway, take a look at all this.  I'm sure you'll have questions.

                          • 10. Re: Linking dates from calendat table to other tables
                            Harry Standley

                            You’re right Joe, a lot of questions. Let me ponder over this for a bit.

                             

                            Thanks,

                            Harry Standley | Business Systems Analyst |  <http://www.georgesinc.com/>  | PO Drawer G | Springdale AR 72765-2030

                            479.927.7127 | Help Desk: 479.927.7777 | harry.standley@georgesinc.com<mailto:harry.standley@georgesinc.com>

                             

                            “Excellent coaches are interested in the success of the individual, not in making mini-me’s anywhere. Therefore, mentoring is about censoring, and coaching is about allowing. Are you mentoring or coaching?”

                             

                            Tipton, Robert S. (2010-04-26). Jump! (p. 197). ALDEN-SWAIN PRESS. Kindle Edition.