1 2 Previous Next 16 Replies Latest reply on Aug 29, 2019 5:08 AM by Zhouyi Zhang

    Calculate conversion rate on a independent date t

    Dimitriy Ni

      Hallo Tableau Community,


      I am stuck with a problem I have an idea how to solve but cant do it in tableau:


      In my case, I want to calculate a conversion rate from status A to Status B of a lead. I have the timestamps when status A and Status B were reached.


      lead_id |  status A      |  status B      |

      1          |  2018-06-01|  2018-06-23 |

      2          | 2018-05-01 | Null               |

      3          | 2018-05-02 | 2018-05-09  |

      4          | 2018-04-02 | 2018-06-10  |

      5          | 2018-05-01 | 2018-06-09  |


      We now want the conversion rate at a time/date t (t is a variable). Conversion rate from A-B in June would be:


      all leads with status B dates in June


      divided by


      all leads that had a date in status A before or in June, that didn't convert into status B before June. So with the data sample above:


      (lead 1,4, 5) = 3


      (lead 1, 2, 4, 5) = 4  = 75% CR in June


      Now I want to do this for let's say all months of 2018 and have a nice bar chart. I'm stuck here because I either have a from data format to do this or I not seeing something. My calculated field looks like that:




      (RUNNING_SUM(COUNT(IF NOT ISNULL(A) AND ISNULL(B) THEN 1 ELSE NULL END)) + RUNNING_SUM(COUNT(IF NOT ISNULL(A) AND B =  ??? here should be t ??? THEN 1 ELSE NULL END)))       <----I need the condition only to count Status A, that have a conversion of B in June, not before! Because they already converted to B before and could not convert to B in June again.


      I need a from the data independent variable t. How to exclude lead 3 from my conversion rate calculation for June?  I can do a CR number for specific months, but I want it running for all months of the year. Any ideas?

        1 2 Previous Next