11 Replies Latest reply on Mar 26, 2017 6:59 PM by Chris McClellan

    Data Blending - Different Years

    ryan.shukis

      We implemented an ERP at the beginning of '16 so we have set up an ODBC connection to it and it pulls in fine to Tableau.

       

      However, we would like to pull in 2015's data (from an Excel file) so we do not have to deal with having everything in Excel anymore.

       

      However, the ERP system does not have the 2015 year in the Date(Year) pill.  Is it possible to blend the data so it creates a continuous year between the 2 data sets?

       

      So if I pull over the Date pill, it will create 3 columns (15/16/17) ?

       

      Thanks!

        • 1. Re: Data Blending - Different Years
          Manideep Bhattacharyya

          Dear Ryan - To blend data between 2016 (ERP Systems) and 2015 (Excel) is little difficult. While Blending on Time series (on Dates) Tableau expects the same dates (Days or Month or Year). You can still do that through a trick.

           

          You need to create one more column in Excel (Where you have 2015 data) to match the date (Day / Month / Year) with your ERP system you want to compare and then blend using that new column of Excel and the ERP system.

           

          This way you can achieve to show 15 and 16 data side by side.

           

          Thanks,

          Manideep

          • 2. Re: Data Blending - Different Years
            Chris McClellan

            Sounds like you're more after a UNION than a blend (basically a JOIN).

             

            You might be able to UNION between the 2 systems (ERP & Excel), by creating a connection, that will depend on exactly what your ERP is and if Tableau can UNION those together.

             

            If the union works, you will end up with all the columns from the ERP and all the columns from Excel and where the columns are the same name (very important for your "time" dimension) you will have a single column with the combined values of both systems.

            1 of 1 people found this helpful
            • 3. Re: Data Blending - Different Years
              ryan.shukis

              What exactly would I put in that column?  I have a date column in the Excel file already that has the dd/mm/yyyy format.

               

              Thanks!

              • 4. Re: Data Blending - Different Years
                Manideep Bhattacharyya

                Dear Ryan - You already have an Excel that has date and some measures. But date range is 2015 right. So create one more column in Excel and put the excel expression as =B2+365 (If your Date field is in Excel column B) and then name that column to current year and blend this with your ERP data and take current year measures from ERP and last year measures from Excel.

                 

                Thanks,

                Manideep

                • 5. Re: Data Blending - Different Years
                  ryan.shukis

                  Thanks Manideep, I think I am almost there now.  Right now an * is showing up for some of the customers.  I have created the relationship based off of date/company.  Would the multiple dates per customer (in 2016) create the one to many accidentally ?

                   

                  And how would I get the years to work?  I am not sure how to get both date pills to work next to each other since right now they stack on top of one another.  By that I mean 2015 is on top, then 2016/2017 are below.

                  • 6. Re: Data Blending - Different Years
                    Chris McClellan

                    ryan.shukis wrote:

                     

                    And how would I get the years to work?  I am not sure how to get both date pills to work next to each other since right now they stack on top of one another.  By that I mean 2015 is on top, then 2016/2017 are below.

                     

                    This is the problem with joining the data.

                     

                    Have you tried the UNION that I mentioned ?   Then you will have 1 year dimension that contains all dates in 2015,2016 & 2017

                    1 of 1 people found this helpful
                    • 7. Re: Data Blending - Different Years
                      ryan.shukis

                      I'll try the UNION next and let you know how it goes.  Thanks!

                      1 of 1 people found this helpful
                      • 8. Re: Data Blending - Different Years
                        ryan.shukis

                        When doing the Union, I can see some customers showing billions of units purchased (not possible in this case - ha!).

                         

                        So this must mean that I do not have the proper columns in the Excel file from PY's data, is that correct?  I keep checking it and everything looks "ok".   Is there something else I should be looking for?

                        1 of 1 people found this helpful
                        • 9. Re: Data Blending - Different Years
                          Chris McClellan

                          It depends on your data.  Pull each data source into Tableau previously to see if the problem happens even before the union.

                          1 of 1 people found this helpful
                          • 10. Re: Data Blending - Different Years
                            ryan.shukis

                            Now that I look at it, is it even possible to have a union to a live ODBC connection?

                             

                            When clicking on the other data sources (excel files), the "create union" link appears but not when I highlight the ODBC.

                             

                            Would it be possible just to insert the union SQL into the custom SQL query that is pulling from the ERP system?  I've never done it before so I'm not sure if it is possible

                            1 of 1 people found this helpful
                            • 11. Re: Data Blending - Different Years
                              Chris McClellan

                              it sounded like you'd done the union, but now it sounds like you can't define the union - it might not be available, it depends on lot on your Tableau version and what you're connecting to.

                               

                              I don't think you can union within the custom sql because you're unioning your ERP to Excel, right ?

                              1 of 1 people found this helpful