Select value in table in tableau

Question

I am quite new to Tableau, so have patience with me :)

I have two tables, Table one (T1) contains all my data with the first row being Year-Week, like 2014-01, 2014-02, and so on. Quick question regarding this, how do I make Tableau consider this as a date, and not as string?

T1 contains a lot of data that looks like this:

 YearWeek   Spend   TV   Movies
 2014-01    5000    42   12
 2014-02    4800    41   32
 2014-03    2000    24   14
 ....
 2015-24    7000    45   65

I have another table (T2) that contains information regarding some values I want to multiply with the T1 columns, T2 looks like:

NAME         TV    Movies
Weight       2     5
Response     6     3
Ad           7     2
Version      1     0

I want to create a calculated field (TVNEW) that takes the values from T1 of TV, and adds Response(TV) to it, and times it with the weight(TV), So something like this:

(T1[TV]+T2[TV[Response]])*T2[TV[Weight]]

This looks like this for the rows:

 (42+6)*2
 (41+6)*2
 (24+6)*2
 ...
 (45+6)*2

So the calculation should take a specific value from T2, and do the calculation for each value in T1[TV]

Thanks in advance

Answer 1

The easy answer to your question will be: No, not natively. What you want to do sounds like accessing a 2 dimensional array and that's not really the intention of Tableau. Additionally you have 2 completely independent tables without a common attribute to JOIN on. Tableau is just not meant to work that way.

I cannot think of a way to dynamically extract that value (I assume your example is just that, an example; and in your case you don't just use two values in the calculation, otherwise you could create 2 parameters that you can use in your calculated fields)

When I look at your tables it looks like you could transpose and join them that they ideally look like this: (Edit: Comment says transposing is not an option)

Medium  Value   YearWeek    Spend
Movies  12      2014-01     5,000
Movies  32      2014-02     4,000
Movies  14      2014-03     2,000
Movies  65      2015-24     7,000
TV      42      2014-01     5,000
TV      41      2014-02     4,000
TV      24      2014-03     2,000
TV      45      2015-24     7,000

and

Medium  Weight  Response    Ad  Version
TV      2       6           7   1
Movies  5       3           2   0

Depending on the systems you work with you could already put it in one CSV or table so you wouldn't have to do a JOIN in Tableau.

Now you can create the first table natively in Tableau (from Version 9.0 onwards), if you open your data source, in the Data Source Preview choose the columns TV and Movies, click on the small triangle and then on Pivot. (At this point you can also choose the YearWeek column click on the triangle and Split to create a seperate field for Year and Week. You won't be able to assign the type date to it put that shouldn't give you any disadvantages.)

For the second table I can think of two possibilities:

1. you have access to a tool that can transpose your table (Excel can do that see: Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize') Once you have done that you can open it in Tableau and join the two tables on Medium

2. You could create calculated fields depending on the medium:

   Field: Weight
   CASE [Medium] 
     WHEN 'TV' THEN 2
     WHEN 'Movies' THEN 5
   END
   

   And accordingly for Response, Ad and Version
   Obviously that is only reasonable if you really just need a handfull of values.

Once this is done it's only a matter of creating a calculated field with

([Value]+[Response])*[Weight]

And this will calculate all the values for your table