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

    Conversion Rate Between Two Date Columns

    sam.hannah.0

      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;

      Lead Date

      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

       

      Leads:

      Leads.png

      Prospects:

      Prospects.png

       

      We have then built out a FIXED LOD

      { FIXED [Leads Date] : SUM([Leads]) }

       

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

      Conversion.png

       

      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
          Zhouyi Zhang

          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
            sam.hannah.0

            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

            THEN [!Leads]

            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

            THEN { FIXED [!Leads - Date] : AVG([!Leads])}

            ELSE 0 END

            )

            }

             

             

            Calculated Field - Adds LOD 2 and LOD 3 together

            [!Leads Conversion Same Month LOD]+[!Leads Conversion Previous Months LOD]

             

             

            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

            DATETRUNC('month',[!Prospects - Date])=DATETRUNC('month',[!Leads - Date])

             

            View Information

            Columns - Primary or main date scale (e.g. !Prospects Date)

            Row - Measure Names

            Measure Values - LOD 1 and Calculated Field

            Filters - Dimension = TRUE