Forum Discussion

Sweet-T's avatar
Sweet-T
Helper III
8 years ago
Solved

Find Earliest Date from Several Columns

Hi everyone,    I have 5 columns, each with various timestamps. I would like to poll each of the rows to find which column contains the earliest date in each row, and then calculate the time betwee...
Need Help
  • Sweet-T's avatar
    Sweet-T
    8 years ago

    Figured it out!

    For all those curious, first I determine the earliest date using: 

    Date Entered Funnel = 
VAR AllDates = {
    ([Entered Stage 1]),
    ([Entered Stage 2]),
    ([Entered Stage 3]),
    ([Entered Stage 4]),
    ([Entered Stage 5]),
    ([Entered Stage 6]) }
    VAR MinDate = 
        MINX ( FILTER( AllDates, [Value] <> 0), [Value] )
    RETURN
        MINX( FILTER( AllDates, [Value] = MinDate ), [Value] )

    Then I calculated the date between the date reported above, and today's date (unless the Job had completed, which I checked first). 

    VelocityTotal All = 
//This function calculates the total velocity for each opportunity.
//It checks if the opportunity has completed first. If so, it reports the time between entering the funnel and completion date.
//If not, it takes the differece between entering the funnel and the current date, and reports it.  
IF(NOT(ISBLANK([Completed Date])), DATEDIFF([Date Entered Funnel], [Completed Date], DAY), 
    DATEDIFF([Date Entered Funnel], TODAY(), DAY))
Sweet-T's avatar
Sweet-T
Helper III
8 years ago

Figured it out!

For all those curious, first I determine the earliest date using: 

Date Entered Funnel = 
VAR AllDates = {
    ([Entered Stage 1]),
    ([Entered Stage 2]),
    ([Entered Stage 3]),
    ([Entered Stage 4]),
    ([Entered Stage 5]),
    ([Entered Stage 6]) }
    VAR MinDate = 
        MINX ( FILTER( AllDates, [Value] <> 0), [Value] )
    RETURN
        MINX( FILTER( AllDates, [Value] = MinDate ), [Value] )

Then I calculated the date between the date reported above, and today's date (unless the Job had completed, which I checked first). 

VelocityTotal All = 
//This function calculates the total velocity for each opportunity.
//It checks if the opportunity has completed first. If so, it reports the time between entering the funnel and completion date.
//If not, it takes the differece between entering the funnel and the current date, and reports it.  
IF(NOT(ISBLANK([Completed Date])), DATEDIFF([Date Entered Funnel], [Completed Date], DAY), 
    DATEDIFF([Date Entered Funnel], TODAY(), DAY))
  • dtartaglia's avatar
    dtartaglia
    Resolver I
    8 years ago

    Hi Sweet-T

     

    I'm glad you explained, I was trying to figure it out your issue but your original code was confusing and I didn't see how it could work on it's own (with or without blanks).

     

    Dan

    • Sweet-T's avatar
      Sweet-T
      Helper III
      8 years ago

      dtartaglia
      No problem. I'll walk though what I intended, since that function has proved quite useful in the past. 

      The basic syntax is: 
      SWITCH (TRUE (), 
      if , then, 
      else if, then, 
      else if, then, 
      else if, then, 

      ...., ...., 
      else)

      So my function checked each Stage to see if the date was earlier than the following Stage ( [Stage 1] < [Stage 2]). If so, it reported the earlier stage. If not, it moved to the next 2 Stages and evaluted them. But if there was an instance where [Stage X] = 0, it couldn't evaluate the expression and returned a null. 

       

      Does that help? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Sweet-T

     

    Can you share your dataset (or a sample of your dataset) too so that it is easy to understand 

     

    Regards