Post

2 followers Follow
0
Avatar

Calculate time spent on category

Hi all,

I have set a category line item for the sales status of a deal (i.e. prospect, qualified, fact finding, quotation, etc.). I was wondering how I find out the amount of time a deal is spend on a certain status. Any help?

Steven Burciaga

Please sign in to leave a comment.

11 comments

0
Avatar

Hi Steven,

this is only possible with a workaround for which you need Podio Advanced Worksflows (or an extension like Globiflow) and one or several additional fields Do you want the amount of time for every status or for one only? I assume, status is a single choice category field, right? Do you use Podio Advanced Workflows?

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer,

Yes, a status is a single choice category field. I would like the amount of time for the current status. I do not know Advanced Workflows or Globiflow. Are they easy to work?

Thanks!

Steven Burciaga 0 votes
Comment actions Permalink
1
Avatar

For Podio Advanced Workflows see here https://help.podio.com/hc/en-us/articles/204366037-Advanced-workflows.
For Globiflow see here https://podio.com/extensions/2

The problem in your case is, that a calculation field can't store the date when a new category is selecteds. Means:
You can do: If status == "prospect" get and show current time.
But if the status changes and the IF-condition is no more "true", than the time value vanishes.

The workaraound would be: Create a second status field with exactly the same options but make it multi choice (put it at the bottom of your app template and name it something like [Status - for Backend use only]). Create Podio Advanced Flows which change automatically this additional status every time you change the normal status: If "prospect" selected, select "prospect" too in the additional field, if it changes to "qualified" select "qualified" too in the additional field. But cause the additional is multi choice there are both options selected ( = they are acitve).

Now you can do a calculation like:
var timeProspect = If additional field contains "prospect" get current time
var timeQualified = If additional field contains "qualified" get current time

Than you can calculate the difference:
var AmountProspect = timeQualfied - timeProspect
Result can be x hours/days/weeks - whatever you want.

The code would be:

var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : "" 
var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" 

var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
"Prospect took " + AmountProspect + " days"

In your calculation field you would see e.g.: Prospect took 5 days

Rainer

Rainer Grabowski 1 vote
Comment actions Permalink
0
Avatar

Thanks!

Do I need to do var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" for each option on the status category?

Steven Burciaga 0 votes
Comment actions Permalink
0
Avatar

You have to do it for each status you want to get the time for, if you want it for only one, you have to do it for this one and for the following one. Let's say you have 4 status options plus the option "closed" and you want the duration for all 4 options:

var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : "" 
var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" 
var timeFact = @additional field.indexOf( "fact finding") > -1 ? moment() : "" 
var timeQuote = @additional field.indexOf( "quotation") > -1 ? moment() : "" 
var timeClosed = @additional field.indexOf( "closed") > -1 ? moment() : "" 

var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
var AmountQualified = timeFact != "" ? timeFact.diff(timeQualified, "days") : ""
var AmountFact = timeQuote != "" ? timeQuote.diff(timeFact, "days") : ""
var AmountQuote = timeClosed != "" ? timeClosed.diff(timeQuote, "days") : ""

"closed" = your last status

Rainer
rg@delos-consulting.com

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

It is saying "The calculation field must reference at least one other field. It cannot be entirely based on static data, such as strings or numbers" Any way to fix this? Sorry for the lack of knowledge.

Steven Burciaga 0 votes
Comment actions Permalink
0
Avatar

Look here how to create calculations https://help.podio.com/hc/en-us/articles/201019358-Calculations

As first line type:
var status = @name of your status field

When you type @ a dropdown list opens, click the status field and it will be inserted

than:
var timeProspect = status.indexOf( "prospect") > -1 ? moment() : ""
and so on

var stands for "variable". In the first line you declare the status field as a variable. So you can use the variable name instead of typing each time @field name.

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

It is coming back wit "Script syntax error: Unexpected token ILLEGAL"

Steven Burciaga 0 votes
Comment actions Permalink
0
Avatar

Maybe it's a typo : Copy and paste you calculation here.

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

var status = @Status (for backend use only)

var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : ""
var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : ""
var timeFact = @additional field.indexOf( "fact finding/assessment") > -1 ? moment() : ""
var timeQuote = @additional field.indexOf( "quotation") > -1 ? moment() : ""
var timeClose = @additional field.indexOf( "closing") > -1 ? moment() : ""

var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
var AmountQualified = timeFact != "" ? timeFact.diff(timeQualified, "days") : ""
var AmountFact = timeQuote != "" ? timeQuote.diff(timeFact, "days") : ""
var AmountQuote = timeClose != "" ? timeClose.diff(timeQuote, "days") : ""

Steven Burciaga 0 votes
Comment actions Permalink
0
Avatar

If you enter the @ you MUST select a field from the list, if you do not you'll get this error notification (also in some other cases) . "@additional field" is invalid if you would substitute "@additional field" by "status" you won't get this notification.

BUT: I've made a very silly mistake, this calculation doesn't work. Instead of creating an additional multi choice category field, you have to create 5 additional date fields and 5 Podio advanced flows. If you want to do this I would show you how. One thing I've to know for that is, if the Status field is a required field, so that the first status option is automatically selected when the item is created.

Rainer Grabowski 0 votes
Comment actions Permalink