5 Replies Latest reply on Apr 26, 2018 4:43 PM by Okechukwu Ossai

    Difficulties creating calculation using two date columns.

    Dennis Tseng

      Hi all,

       

      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:

      1.PNG\

      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)

       

      2.PNG

      //COUNT([FirstTransferDate])/[Accounts Created]

       

      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.