2 Replies Latest reply on Sep 4, 2017 3:42 PM by sam.hannah.0

# Conversion Rate Between Two Date Columns

Using Tableau 10.3.2

We have an issue at the moment where we are wanting to calculate a conversion rate between two different date columns within our data source.

For simplicity these dates are;

Prospect Date

So our conversion rate we are calculating is;

Number of Leads (100) / Number of Prospect (200) = 50%

When we build these tables separately we can see our volumes

Prospects:

We have then built out a FIXED LOD

Which when added to the view gives us the following table:

As we can see our Prospect total numbers are lining up however our Leads total numbers is not. This is because it is including Leads that occur in the following months. July for instance in our volume table is 3,921, we can see this matches to the conversion table in the Prospects Date of July and Leads Date of July. But the total is also including Leads from August.

We have tried a range of different LOD calculations from INCLUDE, EXCLUDE, IF statements within the LOD's, LOD's within LOD's, SUM, AVG, MIN, etc but we cannot get it to just return us the volume for the Leads month. It's become slightly brain melting trying to get this to work.

We want to avoid restructuring the data source as it is used elsewhere or creating another data source which is aggregated.

Has anyone else had a similar issue or can anyone provide some ideas or solutions to getting Tableau to return what we are after.

• ###### 1. Re: Conversion Rate Between Two Date Columns

Hi, Sam

Without a sample workbook, it is not easy to come up with a solution. But what I will suggest based on my understanding is, you need add condition to your fixed calculation.

See my highlight below:

{ FIXED [Leads Date] : SUM(if datetrunc('month',[Lead Date] ) = datetrunc('month',[Prospect Date]) then [Leads] end ) }

Give it a try and let me know whether it works or not.

ZZ

• ###### 2. Re: Conversion Rate Between Two Date Columns

Thanks ZZ, we were already working with a similar idea in mind.

We've eventually figured out a way - though we are still working to see if we can improve how it works.

We ended up building 3 different LOD's to get the desired results.

LOD 1 - This LOD is used for fixing the measure you are converting from to it's date scale, in this example it is Prospects

{FIXED [!Prospects - Date] : SUM([!Prospects])}

LOD 2 - This is the first LOD for calculating your second value, or what is being converted to. This finds the value equal to the same month of LOD 1, in this example it is Leads converted in the same month as Prospect

{ FIXED [!Leads - Date], [!Prospects Date = !Leads Date] : SUM(

IF [Dimension]=TRUE

ELSE 0 END

)

}

LOD 3 - This is the second LOD for calculating your second value, or what is being converted to. This finds the values from the previous months of LOD 1, in this example it is Leads converted from previous Prospect months

{FIXED [!Leads - Date] : SUM(

IF [Dimension]=FALSE

ELSE 0 END

)

}

Calculated Field - Adds LOD 2 and LOD 3 together

Dimension - This is the dimension used within the LOD's, this is used to find LOD 1's date equals the same as LOD 2's date. For LOD 3 it is the opposite