4 Replies Latest reply on Oct 12, 2018 10:42 PM by Kelvin Nguyen

    Data Structure and Table Design

    Kelvin Nguyen

      Hi guys,

       

      Hope you guys can help me out as I really can't get my heads around this.

       

      The data structure that I'm feeding in Tableau right now is as the following:

       

      Year
      MonthAccountAmount
      20181Sales100
      20181Cost20
      20171Sales80
      20171Cost15
      20174Sales90
      20174Cost20
      20184Sales120
      20184Cost30

       

      And currently in Tableau I am creating a lot of calculated fields like "Sales GP" which is basically equal to "Sales" - "Cost", sales difference from last year which is equal to "Sales" of 2018 minus "Sales" of 2017.

       

      So I am wondering, if I manipulate the data structure to be like the following, is it gonna help?

       

      Year
      MonthSales
      Cost
      2018110020
      201718015
      201749020
      2018412030

       

      And then my next question is,

       

      I'm trying to create a basic text table (in Tableau), something like the below:

       

      Metrics
      2018
      versus last year
      versus budget
      Sales GP%

      (equal to Sales GP%

      of this year minus GP% of last year

      (equal to Sales GP%

      of this year minus GP% of budget

      GOR % on Revenue
      Sales GP
      GOR

       

      The tricky thing is Sales GP% is already a calculated field, and "versus last year" is going to be another calculated field which is going to be applied across the column.

      How would you guys work to get this table?

       

      Thanks guys! Really appreciate your help.

        • 1. Re: Data Structure and Table Design
          Jim Dehner

          Good morning Kelvin

          my advice to you is to convert the year and month to an actual date

          assuming that they are numbers and not text use           MAKEDATE([year],[month],01)

           

          if they are string (text ) try           DATE("[year]-[month]-01" )

           

          now that you have actual dates you can use date or table calculations to perform you YOY  and write calculations like (sum(sales)-sum(cost))/sum(sales)  for you GP%

          etc

           

          Jim

          • 2. Re: Data Structure and Table Design
            Kelvin Nguyen

            Good morning Jim,

             

            I don’t think time id matter here Jim

            • 3. Re: Data Structure and Table Design
              Jim Dehner

              That is correct but It will get you around a lot of matchy-matchy conditional statements -

              but you can still use conditional statements and table calculations that match the months and the Years (and year-1) to do what you want

               

              Jim

              • 4. Re: Data Structure and Table Design
                Kelvin Nguyen

                Thanks Jim.

                 

                I still can't get my head around what you're trying to say.

                 

                Perhaps you could help me out by demonstrate it in this workbook? I've attached here a workbook that have 2 data sources. Like I mentioned, the first data sources have the all the figures in one columns and I have one more column to specify whether it's sales or cost etc. The 2nd data source is in another structures, for each dimension (Sales or Cost or Service Income etc) I have 1 column to store the figures.

                 

                What I want to achieve is as the following, hope you could help me out. Thanks Jim.

                 

                                                        2018                         vs 2017

                Sales                                   xx                                  xx

                Cost                                     xx                                  xx

                Sales GP                             xx                                  xx

                Sales GP                             xx                                  xx

                Service Income                   xx                                  xx

                Service Cost                        xx                                  xx

                Service GP                          xx                                  xx

                Service GP%                       xx                                  xx