1 Reply Latest reply on Jan 28, 2013 9:49 AM by Catherine Rivier

    Summing Data At Different fiscal calendar levels (year, month, week and day)

    Jesse LaBoda

      I'm hoping this is a easy question. I have 2 data sources. One of them is from one of our data warehouse (this has sales date in the following format 11/19/2012) and the second comes from a Excel Spreadsheet (this has sales date, accounting week, accounting month and accounting year). I'm trying to associate the sales date from the warehouse with the accounting week, month and year. Then sum those sales at each level when drilled upon. If I show year, month, week and sales date I get the right results. But when I eliminate sales date from the grid it gives me the same result for all periods. Any ideas?

        • 1. Re: Summing Data At Different fiscal calendar levels (year, month, week and day)
          Catherine Rivier

          To join data sources (in Tableau 7 - version 8 is said to eliminate this requirement), you must have the linking field somewhere in your visualization for the join to work.  That means the field must either be in the Rows/Columns/Marks, or in your Level of Detail.  From what you describe, it sounds like your one linking field is Sales Date.  Since that is your only linking field, when you remove it from the view, your link disappears and so does everything else, as you found.

           

          So there are two ways to fix it:

           

          1) Pull Sales Date into your Level of Detail also, so it never disappears from view.  (Not seeing your data here, this might cause overlapping text in your view.  This can be fixed with a calculated field like described here: http://community.tableau.com/thread/110581 and a lot of other places on this forum.  Search for Overlapping Text and FIRST()==0 and you'll find plenty of examples....)

           

          2) Expand the number of linking fields.  Your Excel sheet has sales date, accounting week, accounting month and accounting year.  Your warehouse only has sales date.  Use sales date to create new calculated fields accounting week, accounting month, accounting year.  (DateTrunc will do a lot of the work for you here.  If you have specific issues with these calculations, you can post them here.)  And if you give all of these in the warehouse the same name as the Excel sheet, Tableau will automatically see them as linked, and you won't have to worry at all.

           

          Hope this helps, and let me know if more questions come out of this!

          Catherine