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

Question about moving calculations and manipulating table columns

David Saul Nov 7, 2017 1:27 PM

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	Yesterday	vs Last 15 Days Average	vs Last 4 Same Weekdays Average
Metric 1	1000	x%	x%
Metric 2	1000	x%	x%
Metric 3	1000	x%	x%
Metric 4	1000	x%	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

Joe Oppelt Nov 7, 2017 1:53 PM (in response to David Saul)

A workbook would help answer this.

For instance, is [Metric] a dimension? If so, this should be straightforward.
Like Show 0 Likes(0)

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

David Saul Nov 7, 2017 1:59 PM (in response to Joe Oppelt)

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.
Like Show 0 Likes(0)

Actions
3. Re: Question about moving calculations and manipulating table columns

Joe Oppelt Nov 7, 2017 2:44 PM (in response to David Saul)

Anonymize your Tableau Package Data for Sharing
Like Show 0 Likes(0)

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

David Saul Nov 8, 2017 9:48 AM (in response to Joe Oppelt)
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.

Tableau Help.twbx 61.1 KB
Like Show 0 Likes(0)

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

Joe Oppelt Nov 8, 2017 1:06 PM (in response to David Saul)
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?)

Tableau Help_v10.4.twbx 82.6 KB
Like Show 0 Likes(0)

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

David Saul Nov 8, 2017 3:06 PM (in response to Joe Oppelt)

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.
Like Show 0 Likes(0)

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

Joe Oppelt Nov 8, 2017 3:17 PM (in response to David Saul)
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:

Tableau Help_v10.4 A.twbx 88.8 KB
Like Show 0 Likes(0)

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

Joe Oppelt Nov 8, 2017 3:18 PM (in response to Joe Oppelt)

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.
Like Show 0 Likes(0)

Actions