You basically want to take each row of data and have it affect your view possibly twice (once when they join, once when they leave). That usually suggests you need to pivot your data.
STEP 1 — From the Data Source tab, select your start and end date columns, right click, and choose Pivot.
You'll end up with something like this:
STEP 2 — Rename the "Pivot Field Names" and "Pivot Field Values" columns for clarity:
STEP 3 — Create a calculated field to identify the effect the event has on your running total:
IF [Event] = 'Start Date' THEN 1
ELSEIF [Event] = 'End Date' THEN -1
STEP 4 — Now use SUM([Membership Change]) as a running total, and the [Date] field for your timeline axis, and voila!
Thank you for this information.
Two questions came to mind on this:
1. How can I get every day to appear on my x axis? Even if I don't have a "new subscriber start date" every day, there is a count for everyday. I found that selecting "Show Missing Values" from the header does not solve this.
2. My data is a live connection to a DBMS, can I still do pivots in Tableau off a live connection?
1. A running total table calc, when used in conjunction with a Date axis, will generally trigger data densification (show missing values) automatically. You'll notice that in my workbook, I configured the date as a Continuous Day aggregation (green pill). Configuring the Date field as continuous will also trigger data densification.
2. Tableau cannot pivot off a DBMS, so you will have to do the pivot yourself using a custom query. Looks a bit like this (drastically simplified):
SELECT 'Start Date' AS EventType, StartDate AS EventDate FROM MyTable UNION SELECT 'End Date' AS EventType, EndDate AS EventDate FROM MyTable;
Thank you! The data resides in SFDC. We are using the SFDC Tableau API to retrieve the data and it appears you cannot write queries against it.
Do you know of any easy work arounds to this?
1 of 1 people found this helpful
I've not used the SFDC connector myself, but from what I've read, neither Pivot nor Union features are supported for it. You would need one or the other for this to be straightforward.
Other options mentioned in related Community threads include:
- Writing your own Web Data Connector to pivot the API data appropriately. (Yikes!)
- Prepping your data with an ETL tool like Alteryx. (Robust but expensive.)
- Blending two copies of your data source. (Almost like a UNION, but with serious pitfalls.)
- Using scaffolding in conjunction with blending. (Overcomes the pitfalls of blending, but increases maintenance complexity.)
The last 2 are from the same thread, which contains a very well presented answer from Alex Blakemore. As you can see, this particular scenario is highly reliant on correctly structured data, so getting your data into the form you need is the majority of the battle.
Unfortunately, that is about all I can tell you. I'm sorry I could not be more help!