9 Replies Latest reply on Aug 22, 2019 9:33 AM by Don Wise

    Help me use my access date in Tableau

    Patrick Murphy

      Hi

       

      I have now come to the conclusion that linking my Salesforce.com data to Tableau is a non-starter and given the fact SF and Tableau are now under one roof I have decided (for the the short term) to extract my data into access to pull into Tableau.

       

      I currently have:

       

      Query 1: Closed Case Data

      Query 2: Opened Case Data

      Query 3: Survey Data

       

      Potentially to add: Query 4: Agent Phone State Data (Not ready, after call work etc).

       

      Essentially the above is contact centre data.

       

      There are linkages between the data such as agent name, team name and such like but they can (as I have done with other Tableua books I have created) be treated separately. I would however like to be able to show opened vs closed which I know is possible but will require me to blend (maybe the wrong term) things together, none of which I understand (yet).

       

      I'm looking for some pointers in how I can proceed with pulling this data into Tableau so that I can start to build some nice analysis and trend data etc.

       

      Look forward to some responses.

        • 1. Re: Help me use my access date in Tableau
          KALPIT GOYAL

          Hi Patrick,

           

          I didn't get your exact requirement, you want to blend the data and you don't know how to blend the data in to Tableau?

           

          If yes then please access the below link to do the blending in Tableau:

          Blend Your Data - Tableau

           

          Otherwise, let me know the use case in detail and kindly share your workbook or some screenshot for appropriate answer.

           

          Thanks,

          Kalpit

          • 2. Re: Help me use my access date in Tableau
            Patrick Murphy

            Not quite, although I can look at the link you sent.

             

            Essentially I want to know how best to use the queries I have, so in the first instance what is the best way to link those queries in Tableau to allow me to start looking at some analysis?

             

            Let's think of a basic analysis.

             

            I'd like to show open vs closed cases for a certain date range on one chart, the volume data comes from the Opened and Closed queries for each metric.

            • 3. Re: Help me use my access date in Tableau
              Patrick Murphy

              To add..

               

              So I have closed cases, let's say 10 in May, 20 in June, 30 in July. I would be able to see that from closed date field in the closed data.

               

              I then have opened cases, let's say 15 in May, 25 in June, 40 in July. I would be able to see that from the opened date field in the opened data.

               

              What I want to do is choose a range of dates say, 1st May to 31st July and show the number of closed vs opened in that time frame. I don't want what is contained in the data to determine what I can choose but I don't see how that is possible?

               

              To summarise again I'd like to see dates from 1st May down to 31st July and then Tableau to tell me how many opened and how many closed?

               

              Take one day for an example. Tableau will tell me how many closed, let's say 100, it will also give me a number for opened. Problem is, the number for opened will be wrong because it won't be the true number it will only be the opened on the same day as the closed.

              • 4. Re: Help me use my access date in Tableau
                Don Wise

                Hi Patrick,

                I believe what you're trying to do will involve 'Date/Data Scaffolding'.  The technique is listed here:  FAQ:  Open & Close Dates   Example here: Identification of Active Customers in a Given Period Best, Don

                • 5. Re: Help me use my access date in Tableau
                  Patrick Murphy

                  Hi Don, thanks for the reply.

                   

                  The problem is I have one file for opened cases and another for closed cases, this seems to only want one file with both of those dates on.

                   

                  My simplistic view is that under the measure areas you'd have a "number of records" measure for each file and could drop them into the chart etc.

                   

                  Tableau does seem to like to make things difficult!

                  • 6. Re: Help me use my access date in Tableau
                    Don Wise

                    Hi Patrick,

                    Glad to help if I can...any chance you can supply some mock data that would be coming from the two files?  Hopefully, there's a common key field upon which both files can be joined.  We can then get a better idea of how data is structured and go forward from there... Best, Don

                    • 7. Re: Help me use my access date in Tableau
                      Patrick Murphy

                      Hi

                       

                      Both data sets contain exactly the same fields but just to be clear, they are not the same records. There will be opened cases not on the closed and closed not on the open.

                       

                      I have mocked the data up and also added the fiscal calendar excel file in as well.

                       

                      Hope you can help!

                      • 8. Re: Help me use my access date in Tableau
                        Patrick Murphy

                        I should also add, that for this mock up I have used excel files as my source, I will be using Access due to record limits.

                        • 9. Re: Help me use my access date in Tableau
                          Don Wise

                          Hi Patrick,

                          Not sure about your expected outcome, but please see if attached helps/points you in the right direction?  I pulled off your data each as .csv, joined both on Case Number and then did an outer join to pick up the null values where there isn't a case number in the Closed sheet. Not sure that a date scaffold is needed here yet.  Really depends on what you're looking to do for an end result.  You'll know your data better than me !  I think there's actually 4 things occurring and I've only captured 3 of them; (1) Opened (2) Closed (3) Closed without an Open Case (4) Opened without a Closed Case.

                           

                          So far this is done without any calculations, but I'm thinking to grab number four in addition to the previous three  it'll require a different view or methodology.  I'll get back to this when I can though.

                           

                          Best, Don

                          Screen Shot 2019-08-22 at 9.17.41 AM.png