11 Replies Latest reply on Jul 6, 2018 5:48 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:

       

      COUNT(B)

      /

      (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?