4 Replies Latest reply on Jun 23, 2016 12:44 PM by Christopher McKinnish

Combining Multiple Date Measures Where The Measures Are Separate Columns

Hi Everyone,

Thanks in advance to reading this. So I have this problem where my data is down to one row per customer. It makes the analysis easier (at least to what I am used to). The downside is that there is no date column to trend the data on. This data is held up in separate columns.

Like so:

 customer Customer start date month 1 purchase count month 2 purchase count month 3 purchase count month 4 purchase count month 5 purchase count month 6 purchase count John 2/1/2016 2 8 5 9 6 5 Jane 3/1/2016 2 4 8 3 6 9 Robert 4/1/2016 9 8 5 8 0 5 Joe 5/1/2016 7 7 9 4 9 7

Ideally, my data would look like this to work:

 customer Name Purchase Count month 1 purchase count John 2 month 2 purchase count John 8 month 3 purchase count John 5 month 4 purchase count John 9 month 5 purchase count John 6 month 6 purchase count John 5 month 1 purchase count Jane 2 month 2 purchase count Jane 4 month 3 purchase count Jane 8 month 4 purchase count Jane 3 month 5 purchase count Jane 6 month 6 purchase count Jane 9 month 1 purchase count Joe 9 month 2 purchase count Joe 8 month 3 purchase count Joe 5 month 4 purchase count Joe 8 month 5 purchase count Joe 0 month 6 purchase count Joe 5 month 1 purchase count Robert 7 month 2 purchase count Robert 7 month 3 purchase count Robert 9 month 4 purchase count Robert 4 month 5 purchase count Robert 9 month 6 purchase count Robert 7

Ideally, my sheet would look like so:

But I am having difficulty getting Tableau to see each column as an individual month because they are their own measures.

So my question is, how do I combine these into a single working pill so that it can be used in the chart?

• I had some success in creating a caluclated field that would chop off the "Month" and "Purchase Count" to get the month number into a discrete field in which I could plot that way, but combining the measure, I have no clue where to start.

I have the a sample data set attached. I apologize if this question has come up before, but searching the forum has not been fruitful. So linking me in the right direction would also be greatly appreciated.

• 1. Re: Combining Multiple Date Measures Where The Measures Are Separate Columns

You won't combine them into a single working pill.

Reshape your data so that you have a date dimension to run along.  Separate rows per month.  You'll do yourself a huge favor if you do that.

• 2. Re: Combining Multiple Date Measures Where The Measures Are Separate Columns

Victotr,

If you pivot the month # purchase count columns, you'll get a stack like what you are looking for and you can make whatever manipulations you need to shape the data into a usable date. I used your workbook for an example. Hope this helps.

Long term, reshaping your data will be a lot more efficient, but this might work in a pinch.

Chris

• 3. Re: Combining Multiple Date Measures Where The Measures Are Separate Columns

As Joe and Chris said, you'll want to pivot the data to get the effect you need. This can be done in the Data Source window. Simply select the columns you want to pivot, highlight them, right click and choose pivot. Here's a little more info:

• 4. Re: Combining Multiple Date Measures Where The Measures Are Separate Columns

This does not work with direct database connections.  This works with Excel, but not IBM.  Just as info.