3 Replies Latest reply on Mar 23, 2016 7:06 AM by harris.tsr

    Create Date calculated field based on difference metric




      I'm creating a dashboard which will include 6 graphs, showing the avg number of days that takes for a lead to be converted to opportunity, sales, etc What I'm trying to figure out is how I can create a calculated date filed that will take for each graph the correct reference date. For example, in graph 1 below, axis X shows the range of months based on the date that lead was created (mql date).


      Graph 2, shows the avg number of days it takes a converted lead to SAL to be converted to an SQL (ignore the terms naming, call it whatever you want to make sense).

      In this case I want the axis X, to reflect the months range based on the SAL date, which is the date that the lead was converted from being an mql to sal.


      For example, if we initially had a lead created (MQL) on 1March (MQL date) and was converted to an SAL on 15April (SAL date) I want the second graph's axis-X to take as starting poing month April, not March. I could use different date sliders for each graph, but it would create so much confusion and I don' thtink it would give consistent insight to the users. So, in my mind, I want to create a reference date based on the metric each graph shows. For example, in the first graph the blue graph line shows the average of the measure AVG(MQL_TO_SAL) which is a measure field coming from the datasource. In second graph, the green line shows the average of the measure AVG(SAL_TO_SQL).


      Thus, I don't know if it is doable, I want to create a calculated date field like:

           IF [Measure]= AVG(MQL_TO_SAL) THEN [MQL date]

           else IF[Measure]=AVG(SAL_TO_SQL) THEN [SAL date]


      Hope it makes sense..Any feedback much appreciated