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

Difficulties creating calculation using two date columns.

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:

\

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)

//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.

• 1. Re: Difficulties creating calculation using two date columns.

A  packaged workbook with fake data  is better than detailed description so much . Please attach a fake data  workbook.

• 2. Re: Difficulties creating calculation using two date columns.

Sorry, I realized I forgot to include the workbook just a few minutes ago. It should have been attached already.

• 3. Re: Difficulties creating calculation using two date columns.

Hi Dennis,

One approach will be to use LOD to calculate the count of created and acquired IDs, fix it for each date and then ID. Tableau performs row level calculation, so you need the correct acquisition and creation counts to be on the same row.

Step 1: Create calculated field [Number Created]

MIN({FIXED [ID]: MIN(IF [Acquisition Date] = [Account Creation Date] THEN {FIXED [Account Creation Date]: COUNTD([ID])} END)})

Step 2: Create calculated field [Number Acquired]

MIN({FIXED [ID]: MIN(IF [Acquisition Date] = [Account Creation Date] THEN {FIXED [Acquisition Date]: COUNTD([ID])} END)})

Step 3: Create calculated field [Activation]

[Number Acquired]/[Number Created]

Hope this helps.

Ossai

1 of 1 people found this helpful
• 4. Re: Difficulties creating calculation using two date columns.

Ahhh that's it! Definitely need to do more learning about that type of calculations.

Thanks a bunch!