Hi,
I'm pretty new to Tableau and working with pivoted data for the first time. I have pivoted table with customer data which looks somewhat like this:
IDActionDateFields.... (Age, Gender, etc.)
1Enrolled01.01.2017...1Cancelled01.01.2018...2Enrolled01.01.2017...3Enrolled01.01.2017...3Cancelled02.01.2017...
To look how the customers developed over time I used three calculated fields which look like this:
[Customers enrolled] = IF [Action] = "Enrolled" THEN 1 ELSE 0 END
[Customers cancelled] = IF [Action] = "Cancelled" THEN 1 ELSE 0 END
[Cutomer difference] = RUNNING_SUM(SUM([Customers enrolled])) - RUNNING_SUM(SUM([Customers cancelled]))
This works great so far and I was able to do a lot of visualizations.
Now i want to look at the customers who are currently (still) enrolled and analyze them further. How would I go about doing that?
I tried filtering [Cutomer difference] but that doesn't work bescause it's a running sum. I selected the last month in my view and tried to create a set but in that case most customers where missing.
Basically what I want Tableau to do is: Take all enrolled data rows where the corresponding cancelled row lies after date X or doesn't exist. And have that as a measured or calculated field for further use.
Is there any way to do that?
Thank you very much for the help.
Hi Victor,
Is it possible for a customer to enroll, cancel and then enroll again? Whichever case, the solution below will work for you.
1. Create calculated field [Last Enrolled]
{FIXED [ID]: MAX(IF [Action] = 'Enrolled' THEN [Date] END)}
2. Create calculated field [Last Cancelled]
{FIXED [ID]: MAX(IF [Action] = 'Cancelled' THEN [Date] END)}
3. Create calculated field [Active Customer?]
IF ISNULL([Last Cancelled]) OR [Last Cancelled] < [Last Enrolled] THEN 'Yes' Else 'No' END
4. Create calculated field [# Currently Enrolled]
COUNTD(IF [Active Customer?] = 'Yes' THEN [ID] END)
There are different ways you can use calculations 1 and 2 to solve different problems. I'd leave that to you. Steps 3 and 4 are just one way.
See attached workbook in Tableau version 10.1
Hope this helps.
Ossai
