I'm looking to show active members over time using a database of accounts and subscriptions with expiration dates.
Here is what the data looks like:
Account IDPurchase IDStarted DateExpiration Date112016-01-012017-01-01122017-01-012018-01-01132018-01-012019-01-01242018-01-01
2018-02-01
252018-02-012018-03-01262018-03-012018-04-01372018-01-012019-01-01Essentially, I want to show a continuous timeline that will show me each day how many active members there are.
For instance, up until 2018 Account ID 1 would show a count of 1 active member. Starting in 2018 I would jump to 3, and after 2018-04-01 is would show 2.
I have both monthly and yearly subscriptions in the dataset and I want to count the distinct accounts to see the growth over time.
Any ideas?
This is a relatively common type of question - and one common answer is to try and get the data in a slightly different format, with one row for each event (starting or stopping the account).
You can do that by creating a Union on your data; from the Data pane you would drag and drop the table on-top of itself giving you something like:
That will give two copies of each row - we're going to use one to count accounts opening and the other to count closes. It also creates a new field 'Table Name' to show which copy of the data the row came from.
The next step is to create some calculated fields to do this. We're going to make three fields:
1. Change Type: if [Table Name] = 'Sheet1' then 'Start' elseif [Table Name]='Sheet11' then 'End' end
2. Change Date: if [Change Type] = 'Start' then [Started Date] elseif [Change Type]='End' then [Expiration Date] end
3. Change in Net Active: if [Change Type] = 'Start' then 1 elseif [Change Type]='End' then -1 end
The key part here is the Change in Net Active - which will count 1 for the records which correspond to the start date and -1 for the expiration date.
If we add Change date to columns and Change in Net to rows it will show how the total has changed on each day. To see the impact on overall active accounts we'll turn it into a running total (Right click on the pill and select Quick Table Calculation > Running Total)
