Forum Discussion
Sweet-T
Helper III
8 years agoFind 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...
- 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
Helper III
8 years agoFigured 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))Anonymous
8 years agoNot applicable
Can you share your dataset (or a sample of your dataset) too so that it is easy to understand
Regards