4 Replies Latest reply on May 16, 2018 8:39 AM by Lauren Anderson

    Prepping Data

    Lauren Anderson

      Hello,

         I'm working on prepping data for an organization-wide dashboard. The aim is to look at the annual goal, YTD goal and YTD Actual for various indicators. Below is a snapshot of the spreadsheet with the data; however, I'm not sure if the data is set-up correctly. From what I know of Tableau, indicators typically have their own columns but I could be wrong? A concern I have with this format is I won't be able to separate out the YTD goals to match with the different Leading Indicators.

       

      As a beginner user of Tableau, I'm still learning how to organize data in order for it to be pulled into Tableau so any advice will be much appreciated!

       

        

      Leading IndicatorAnnual GoalYTD GoalYTD Actual Variance
      Revenue 21,551,000 15,944,972 14,018,761 (1,926,211)

       

       

       

      Thanks!

      Lauren

        • 1. Re: Prepping Data
          Jim Dehner

          Hi Lauren

          Tableau is database oriented (v cell orient like Excel) - the ideal structure is a tall thin table (v short and wide)

          see below-

           

          each column will form a dimension or measure -

           

          data comes in Tableau as Disaggregated and is Aggregated during the creation of the viz

          monthly would be preferred and the data would be summed as it is placed on the viz

          Also bringing in actual dates supports text tables - line or bar charts and can be used to aggregate YTD, MON.YOY analyses

           

          Thanks for asking

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Prepping Data
            Lauren Anderson

            Thank you Jim! Knowing I'll need to add an "Actual/Goal" column is very helpful.

             

            To follow-up on the format you included above, if Annual Goal and YTD Goal need to be displayed, how would you format these? I've added an Actual/Goal column but not sure how to describe it to differentiate between Annual and YTD.

             

             

            Thanks!

            Lauren

            • 3. Re: Prepping Data
              Jim Dehner

              Ok

               

              Busted - I realized as I put the attached together that I lead you down a bad path

               

              Suggesting that you put an Actual/Goal column is just going to compound problems for you  - it is much easier to have 2 sheets in your workbook - I for Goal and 1 for Actual -

              Probably make you goals annually anyway so that sheet does not change - the Actual sheet would update monthly and you would just refresh the data

               

              You would then join the 2 sheet on Data and Metric (I only had one metric so there is only 1 clause)

               

               

              the full year goal is this

               

               

              the monthly % to plan

               

              the % to full year goal

               

               

               

               

              Jim

              • 4. Re: Prepping Data
                Lauren Anderson

                Okay, I think I'm beginning to understand it.

                 

                Still confused on how to separate out Annual Goal, YTD Goal and YTD Actual. From your example above, only one "goal" column is displayed while Full Year Goal, Monthly Goal and YTD actual displayed as  measures you created. However, we have our Full Year Goal & the Monthly Goals already established; therefore, I don't think we'll need to use equations to find those values. Correct me if I'm wrong... 

                 

                Would it work for me to create on the second sheet, a Monthly Goal and an Annual Goal as separate columns? and on the other sheet, it would just be the Actual column?

                 

                 

                Thanks so much for your guidance thus far!

                 

                 

                Lauren