1 of 1 people found this helpful
Tableau won't allow you to create rows the way you are looking for. There is a technique using data padding and a scaffold data source, but even that will need you to do some work in Excel.
The best way, ideally, would be to reshape the data in Excel, with the end result looking like the following:
Month Product Amount Jan-11 P1
Jan-11 P2 200
Note that this is only a breakdown of month, not exact days (some of your subscriptions started and ended on days during the month).
If this isn't possible, however, what do you think you are able to get to easily? Is the reshape possible, or do you need a different solution?
Thank you very much for your quick answer.
Reshaping my data the way you are suggesting works perfectly, Tableau aggregates the revenue and creates a very nice chart!
However we want to do the pre-processing step automatically, so I was considering using a database to store the data I have in the excel file, then use a store procedure and SQL clauses to reshape the data and store it in another table the way I need it and make the process automatic.
Andrew is there any other more efficient, less time consuming solution that you could suggest me?.
Also where I can get more information about padding technique and scaffold data source?
Thank you very much.
Do a search on padding or scaffolding on the forums and you'll see a number of different posts.
In your case, all you need is a table with a column of months and a cross product query that looks something like this pseudo-sql:
table1.Subscription Start Date,
table1.Subscription End Date,
FROM Months, table 1
WHERE table1.Subscription Start Date >= Months.Month AND table1.Subscription End Date <= Months.Month
You could do this in Custom SQL in Tableau on the Excel file (just add another worksheet with the Months).
That will give you a row for every product and month is has Revenue in.
Then you'd create the table 2 in Tableau using Month on Rows and Product ID on Columns, with SUM(Monthly Revenue) on the Text Shelf.
That works just perfect!