2 Replies Latest reply on Jun 7, 2017 11:46 AM by Brandon Isner

    How to best set up an ongoing Excel spreadsheet for quarterly reporting?

    Brandon Isner

      Greetings all,

       

       

      First post. I have an assigned goal of moving much of the reporting I do into an ongoing spreadsheet which I can update within Tableau. I've read through a lot of advice pages, including this one, but can't seem to find the answer.

       

      Attached is a very basic example of how our data has been kept. There are dozens upon dozens of other fields that we report on, but it's always in this basic format, with the quarters on top, for ease of entry. What I would like is to be able to set it up in a way where I'd not only be able to create instant graphs on the various rows, either as single measures or combining some, across a set of time periods (i.e. being able to switch between yearly or quarterly reporting would be ideal, and able to chart either 5 years, or 6 quarters, or the last two, etc.).

       

      Hopefully, once set up, I could just continue adding time periods and data at the end, and keep building it over time.

       

      Advice? Any would be appreciated.

        • 1. Re: How to best set up an ongoing Excel spreadsheet for quarterly reporting?
          Wilson Po

          Hi Brandon,

          I would actually recommend putting the data in this structure:

             

          DatesClassabsrates
          Q2 2016A63,312 $    21.07
          Q2 2016B2,500 $    16.87
          Q3 2016A(214,856) $    21.15
          Q3 2016B(46,299) $    17.22
          Q4 2016A118,480 $    21.23
          Q4 2016B39,231 $    17.39
          Q1 2017A56,382 $    21.30
          Q1 2017B14,886 $    17.50

          This organizes the data so that each column represents the same consistent definition and can be aggregated together easily.  I would also recommend removing the totals - Tableau can recompute those easily, and having it in the data can duplicate the values.  The only thing that isn't clear is how to represent the Total rates - not sure if that can be computed based off the class A/B rates or whether it is only something that lives separate. Either way, hope this helps! 

          1 of 1 people found this helpful
          • 2. Re: How to best set up an ongoing Excel spreadsheet for quarterly reporting?
            Brandon Isner

            Yep, that works. Hard to get away from thinking about the data in the other way.

             

            Yes, I'll have to figure out the rates, as they are definitely weighted, but that is a small price to pay. Awesome, thank you.