Have something that's been perplexing me and a couple members of my team, thought I'd throw our problem to see if anyone could give us any ideas.
We have a dataset that has 3 main columns: an Id, and two date columns (AccountCreationDate and AcquisitionDate). The distinction is that Column B (AccountCreationDate) can be a date before the Acquisition Date. As shown in Ids 5-11, the account creation date is in March, while the acquisition date is April 23:
The main thing though, is that Column C can be NULL instead of having a date... Unfortunately, this has led to the issue that we can't solve:
We've been trying to create a visualization that shows for the last X date units (let's just use days in this example), activation rate, which is calculated by:
(Count of all Ids that were acquired at a given date)/(Count of all the Ids that were created at the same given date)
So in the above case, April 23rd's "activation rate" would be 9/4 = 2.75 (275%), April 22nd's would be 7/3 = 233.33% etc.
What we've tried is to create a calculation that would count the totals based on the two dates:
COUNTD(IF DATETRUNC("day",[Acquisition Date])>=TODAY()-5 THEN [ID] END)/COUNTD(IF DATETRUNC("day",[Account Creation Date])>=TODAY()-5 THEN [ID] END)
This calculation works if you don't add any additional columns. However, when I try and put a date column to break the rates down by day, the numbers are not correct:
As seen above, for April 21, instead of 6/4 = 150%, it's 3.0 = 300%. The key issue, is that if I use any of the two date columns to display the visualization, it restricts the data:
- If I use Account Creation Date, for the count of Acquisition Dates within the last 6 days, I restrict all those that did not have an account creation date in the last 6 days.
- If I use Acquisition Date, I restrict all the Ids/Rows that do not have an Acquisition Date for Account Creation.
The dirty workaround would just be to create a bunch of one bar visualizations and piece them together in the dashboard, but I definitely want to avoid doing that.
Is there a workaround or solution that anybody can think of?
EDIT: Woops, forgot to attach the workbook.
Example Workbook.twbx 114.2 KB