11 Replies Latest reply on Mar 3, 2017 9:27 AM by Béatrice LORIOT

    date blending

    Béatrice LORIOT

      Hi everyone, looking for help on date blending.

       

      I have 3 tables which can not be joined (Here replicated in Xls to get support)

      1/ Customer table with ID, creation date and modification date

      2/ Provider table with ID, creation date and modification date

      3/ Product table with Customers ID and Providers ID

      I would like to know - for each active product how many providers and customers have been "touched" (created or modified) between 9 and 15 of January.

       

      I have created a calculated field for the date to be considered :

      Consider the modifcation date, and if null, then consider the creation date.

       

      There can be a date where a provider is created, but no customer, and vice versa.

      I need to see all Customers and Providers created during that period for each product.

      Enclosed twbx.

      Thanks !!!

        • 1. Re: date blending
          Shinichiro Murakami

          Hi Beatrice

           

          For data blending , you need to prepare master data table.

           

          In this case, you need to have all the combination of Dates x ID. So first create below table.

           

          From Data source edit, pivot data

           

           

          Edit relationships as followings.

           

           

           

          Then, create table

           

           

          Thanks,

          Shin

          • 2. Re: date blending
            Béatrice LORIOT

            Hi Shinichiro,

            Thanks a lot for taking the time.

            I think my problem comes from the fact that tables come SalesForce API and I am not able to pivot the data as suggested to be the first step.

            i am blending the dates as per your suggestion but I still can't get the correct number.

            I was hoping we could find a solution with a LOD cc.

             

            Unfortunately, I am not able to share a twbx of SalesForce Table and was hoping reproducting tables in XLS would help

            • 3. Re: date blending
              Karthik Venkatachalam

              Hi Beatrice,

              I have run into this exact scenarios prior. It is not possible to do "exactly" what you have asked, because, for Blending to work, you need a common field/ Relationship which would work only to a depth of first level. But in your case, you have a link at 2 levels deep. Tableau doesn't recognize this. If you look at what I have done below, I have created link between "Providers" and "Customers". So for "Dates to consider" filter works like charm, because you are blending both views. But for ID to link to Provider and Customer, that is a "second level" abstraction. You should be able to link them successfully as well. But when it comes to interactivity and filtering, First level link and Second Level links will not connect .

               

              Which means, you cant filter down to all the way deep. Rather, you would have to Click on "Product" and choose "date ranges" both to answer the question you are asking. i.e, use dashboard filter action and pass the ID field on to the Provider and Customer view. This should still help you answer the question. But definitely not an intuitive way to build the analysis.

               

               

              Problem is this data is NOT well structured. I have seen this exact situations prior. Where the data structure is poor and there is no way to do the analysis with these 3 tables. Even if it did allow for any analysis, then it is the "un-intuitive" way to interact and filter.So, if you are very clear about your objective, then you need to Join/ Link/ Flatten these 3 tables in a way that would allow your analysis. You can do it yourself or reach out to your SQL developers.

               

              (Note: Please remember to mark it answered, if this helps).

              2 of 2 people found this helpful
              • 4. Re: date blending
                Béatrice LORIOT

                Hi Again Shinichiro,

                Can you resend the twbx as it opened errors in fields

                Moreover, If I look at your screenshot, this is not the expected results, I think you are facing the same issue as I do.

                Thanks

                • 5. Re: date blending
                  Béatrice LORIOT

                  Hi Karthik,

                  Thanks a lot. That's really helpfull ! I will check and see what I can do !

                  • 6. Re: date blending
                    Béatrice LORIOT

                    HI Again,

                     

                    What if we would ignore the ID, and just want to sum all Providers and Customers created within the period ?

                     

                    To start with, have a total of 17 customers and 6 providers for 2017; Could we then add the products ? Or is it a 3 rd level as well ?

                     

                    Thanks

                    • 7. Re: date blending
                      Karthik Venkatachalam

                      I have laid out the view a little different to convey what I wanted to say, regards to "depth". Left side, Product would allow you filter one level....and on the right pane, "Dates" would allow to filter both providers and customers. However, both do not go across the middle line because, that level of link is "beyond reach". (However you could force that level of filtering, by creating a dashboard filter action and passing ID/Provider/Customer fields across).

                       

                      That said - To answer your question - Product would still be another level down. So you would not be able to add, unless you "flatten" the table (or do a "master table" as Schinichiro).

                       

                       

                      If Schinichiro's approach works, well and good. But my guess, looking at it is - it may work for smaller dataset, but for larger datasets, my personal opinion is to tell your team to transform/ flatten the Dataset in source system/ ETL layer, prior to bringing it in. If its a salesforce API, then you could bring in all 3 tables, Stage them in a temp database, and Create a  Flat reporting table. This would help avoid any other potential data issues.

                       

                      Also, flattening the table can cause relationship issues and duplicates. You can write LOD's to handle any possible duplicates. {FIXED Key : Max/Min/ Avg(measure)}  etc.

                      • 8. Re: date blending
                        Béatrice LORIOT

                        Hi again,

                        I am sorry If i am being extra silly, but , as per previous question, how do I get to see the total providers and customers, ie 17 and 6 together, even if we disregard ID and product :  Is it possible to do kind of an "outter join" and get all dates from both table, and total of both providers and customers ??

                        • 9. Re: date blending
                          Béatrice LORIOT

                          I got a beginning of answer from Norbert Maijoor

                           

                          The solution to first get all dates (outter join) from the 2 tables (customers and providers)  is to use scaffholding : https://community.tableau.com/message/586467?et=watches.email.thread#586467

                           

                          Very kind regards to all and thank you for your time !

                          Béa

                          • 10. Re: date blending
                            Karthik Venkatachalam

                            Thank you for Sharing Beatrice. I am not fully sure how this is a robust (and even a tableau) solution. Firstly, wondering how you just "Create" a table magically within tableau that would just keep everything (dimensions) in sync. For most projects I have been involved "Dimensions" change constantly. New elements come in, and old ones drop out on a constant basis. My belief is some of these band-aid solutions might work for a $2 apples and oranges problems. But, without a ETL/SQL transformation Layer, I don't see a robust solution coming out of these. Given these days, people expect a single tool to do everything and that boundaries are so vague, its even harder to classify the problems into separate baskets, which in here, between Source DB, ETL and Tableau. But I like to classify which basket can handle the problem well. I would love to see if you are able to accomplish your goals with scaffolding approach. If you would be kind enough to do a write-up with how you accomplished it, may be the community could help this as well. Your write up can convince other future travelers to adopt a "scaffolding" solution.

                            • 11. Re: date blending
                              Béatrice LORIOT

                              Hi Karthik,

                               

                              Thanks for your answer.

                              The thing is that none of the solution proposed in this thread was able to give me the result I was expecting : total of 17 customers and 6 providers for 2017;

                              After your comments, I raised a ticket and Tableau offered the Scaffold solution as well :-(

                               

                              i know the solution is to have a better database but what you do when you can't pivot data and you can't do SQL : I am connected to Salesforce API with Tableau connector and this does not allow any of those solution.

                               

                              I have been able to make some progresses though, and now have the following fields in only one table instead of separated tables before.

                              Customer ID

                              Provider ID

                              Customer creation date

                              Provider creation date

                               

                              So I have been able to make a work around with the following calculated field :

                              IIF(YEAR([Provider creation date])=2017,[ID Provider)], NULL)

                              Same for Customer

                              And then sum

                               

                              But I can not filter by month or quarter any more....   :-(