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

# Data Structure and Table Design

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

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

Good morning Jim,

I don’t think time id matter here Jim

• ###### 3. Re: Data Structure and Table Design

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

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