2 Replies Latest reply on Oct 25, 2015 4:38 AM by Shaun Paterson

    Cross-tab KPI reporting - where to start?

    Shaun Paterson

      Hi folks,

       

      I hope someone can point me in the right direction. I had thought that this report was going to be fairly easy but I'm totally thrown and cannot figure out how to do it.

       

      The concept is simple. Sales people are meant to go out to small stores and get them to sell phone-cards (could be selling anything). Once they have visited the store, the store details are added to a database.

       

      So they start with zero stores and over time they build their territory. They have a target of visiting all of their stores twice a week to give them more cards to sell. The KPI (Key Performance Indicator) is 85%, i.e. they should meet this target at least 85% of the time.

       

      I have to present the data in a cross-tab format similar to below. I need to calculate how many sites there are which should be visited each week and then calculate how often they have been visited in that week.

       

      SLA 85%
      NationwideActualTotal SitesVisited more than twiceVisited twiceVisited onceNot visited
      Week to date85%4023251
      Oct 12-1888%3532822
      Oct 5-1173%3022026
      <previous>88%2512121

       

      My problem is that the data I have comes in on two Excel Tabs. One tab has the date on which the Store is first visited. The second tab has a record of all the site visits. So we have all the data, but my brain is imploding as I cannot figure what to do. I'm doing a join on the site Name. And I can work out how many sites should be visited each week.

       

      I think that this can be fairly easily managed in Excel, though probably having to use Pivot Tables to summarize the data. But with Tableau I'm not sure where to start.

       

      I know it's a huge imposition but could someone direct me to a starting point? I'm sure other people have done this in a million different ways. I just can't figure out the right search words to get some good examples to learn from.

       

      Thanks very much for your help... Shaun

        • 1. Re: Cross-tab KPI reporting - where to start?
          Daniel Vincent

          Is there any way you can upload a sample set of anonymized data to show what you're working with?  It will make it easier help you in shaping it to a useable format in Tableau which solves for what you need.  Else, we are going to be suggesting things that will become trial and error.

          • 2. Re: Cross-tab KPI reporting - where to start?
            Shaun Paterson

            Hi Daniel,

             

            Please find attached the spreadsheet I'm working with.

             

            The POS Details for Tableau tab has the sites and the dates on which they have been created. The POS Reports for Tableau has all of the reports as they have come in. The initial Report for a site has the same date/time as the POS creation date.

             

            Original data didn't break down the dates in to weeks or months, but I have used Excel formulas to get that information to make it easier for analysis in Tableau.

             

            If you could have a look I'd be very grateful.

             

            Many thanks... Shaun