2 Replies Latest reply on Apr 8, 2013 6:43 AM by Ellen Pfeiffer

# Comparing Two Date Fields

Hi, I have a data set where each person has two date fields- SignUp_Date and Cancel_Date

I want to show how many people signed up and how many people cancelled on any given day, and then the net growth. However, since the two dates are mutually exclusive, I can't figure out how to show how many of each happened each day.

If I use Cancel_Date, it will just count how many people also have a SignUp_Date (which is everyone). If I add a calculation that says only when Cancel_Date = SignUp_Date then it will look for individuals who signed up and cancelled on the same day. And vice versa for Signup Date.

I need to compare them as if they weren't connected to each other.

Any ideas?

• ###### 1. Re: Comparing Two Date Fields

There are many ways to tackle problems like this, ranging from very simple to extremely complex, depending on refined requirements.

If I understand the problem correctly, you just want to count for each day how many users have signed up and how many cancelled, and then calculate the difference. If your data is in Excel, then the simplest way to do it is to re-shape the data using Tableau's Excel add-in (as explained here: http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel) and do a few simple calculated fields. I attached the spreadsheet with reshaped data and the workbook with a solution.

If, however, you want to know how many active users there were on a given day, (i.e. how many signed up on that date or before and haven't cancelled yet), and/or your data cannot be reshaped for whatever reason, then this requires data padding/densification and other acrobatic maneuvers that can get quite complex.

• ###### 2. Re: Comparing Two Date Fields

This worked perfectly, thanks!