I'm looking for a push counter that works on quarterly time period (not monthly as the standard push counter offered by Salesforce lab).
I've reached out to SF support and they have created the following formula:
Push Counter Field:
IF( MONTH( CloseDate )= 1 , 0,
IF( MONTH( CloseDate )= 2 , 0 ,
IF( MONTH( CloseDate )= 3, 0 ,
IF( MONTH( CloseDate )= 4 , 1,
IF( MONTH( CloseDate )= 5 , 1 ,
IF( MONTH( CloseDate )= 6 , 1 ,
IF( MONTH( CloseDate )= 7 , 2 ,
IF( MONTH( CloseDate )= 8 , 2 ,
IF( MONTH( CloseDate )= 9 , 2 ,3
)))))))))
But the following things are still not working and they weren't able to solve it:
-it should also count in quarters that you have jumped over: So if you are in q1 and then move the oppty into q3 you have in fact pushed back 2 quarters so count should be 2 not 1
-also if I pushing further than current year e.g. pushing it from q3 2018 into q1 2019 the counter is suddenly back at 0 again. It should always keep on counting, in the end with the counter we want to track the #.of quarters an oppty got pushed back in total until oppty got finally closed.
Many thanks for your help,
Philipp
4 respostas
Hi Philip,
Do you only want to push counter if the CloseDate is moved forward or do you want it for backward movement too?
Assuming you only need it when the Close Date is moved forwards, the formula will be as belowI am assuming you are using this formula in a field update either via workflow or process builder, please give this a shot
IF(CloseDate < PRIORVALUE(CloseDate), NULL,
IF(YEAR(CloseDate) = YEAR(PRIORVALUE(CloseDate)),
CEILING(MONTH(CloseDate)/3) - CEILING(MONTH(PRIORVALUE(CloseDate))/3),
(YEAR(CloseDate) - YEAR(PRIORVALUE(CloseDate)))*4 - CEILING(MONTH(PRIORVALUE(CloseDate))/3) +
CEILING(MONTH(CloseDate)/3)
)
)
