8 Replies Latest reply on Nov 8, 2017 3:18 PM by Joe Oppelt

# Question about moving calculations and manipulating table columns

Hi Tableau Community-

I am trying to construct a table with about 15 metrics running down the table as rows, with specific moving calculations as columns. The table below is a mockup of what I'd like it to look like.

Metric
Yesterdayvs Last 15 Days Averagevs Last 4 Same Weekdays Average
Metric 11000x%x%
Metric 21000x%x%
Metric 31000x%x%
Metric 41000x%x%

I've done a lot of research on this and I'm familiar with LOOKUP functions, WINDOW functions, and table calculations as ways to compute the percent comparisons noted in red in the mock table below. However, the problems I'm running into are:

• If I use LOOKUP and WINDOW functions in a calculated field, will I need to make a separate calculated field for each metric?
• In any event, whether I use table calcs or create my own calculated fields, I can only seem to add them to the table as new rows. How can I add these as new columns, with each value lined up with the row for the "parent" metric?

Thank you in advance for any help. This would be simple to do in Excel, but seems very complicated in Tableau.

• ###### 1. Re: Question about moving calculations and manipulating table columns

A workbook would help answer this.

For instance, is [Metric] a dimension?  If so, this should be straightforward.

• ###### 2. Re: Question about moving calculations and manipulating table columns

Thanks for the reply Joe. I'm trying to figure out how to share the workbook without sharing the underlying data for client privacy reasons. But to answer your question, [metric] here is actually about 15 separate measures, not a dimension. And that's what is giving me problems. Since they're all measures, I can't figure out how to assemble them into the desired table format.

• ###### 4. Re: Question about moving calculations and manipulating table columns

Thanks for the link Joe. I've attached here a barebones sample workbook with anonymized data. As you can see, the data is a list of metrics by day, and sheet 1 has a simple table of that data filtered to only show the last day. This is the table where I'd like to add the moving calculations as additional columns, for each of the metric rows. Thank you so much for any help.

• ###### 5. Re: Question about moving calculations and manipulating table columns

In the attached, I made a copy of your data source, and then I right clicked on the data source and selected EDIT DATA SOURCE.  In there I selected the 10 measure fields, and did right-click, and selected "Pivot" as shown in the screen shot:

Look at Sheet 2.  You'll see what Tableau does with this.  You get a dimension that contains the field names, and a measure that has the measure values.

Now you can use measureNames/measureValues as I have done on Sheet 2.  I also made a calc that averages the prior 15 days.  You can make a calc to do the last 4 same weekdays.  (Do you need help figuring that out?)

• ###### 6. Re: Question about moving calculations and manipulating table columns

Joe -

Thank you, that's exactly what I was trying to do. However, the only problem is: in the "real" workbook, I extract the data from Google Analytics, not an Excel file. Therefore, in the edit data source screen, there's no option to manipulate the data in the way you did here. Since the data has to be pulled from Analytics as-is, it seems like there's no way to edit it like this before using it. Do you have any advice to offer for that scenario?

And thank you again so much for your time and effort on this.

• ###### 7. Re: Question about moving calculations and manipulating table columns

Right.  Not all data source types let you do that.

Ummm...

I think you'll need to separate calcs for each metric.  (15 day for metric 1.  15 day for metric 2.  Etc.  And last 4 same weekdays for metric 1, last 4 for metric 2, etc.)  And then you'll have to create separate sheets for 15 day and for last 4.  On the extra two sheets you wouldn't display measure name.  Just the numbers.  And then you would place the three sheets side by side on a dashboard so that they end up looking like one viz.

Like what I started doing on the dashboard in the attached:

• ###### 8. Re: Question about moving calculations and manipulating table columns

I don't know how large the Google data set will be, but maybe you can dump it into an excel file before sending it to Tableau.