calculate time passed between category changes + map the successive categories
Hi all,
I've created a lead funnel in Podio, where all incomings leads arrive "unqualified", then move on to "qualified" or "rejected", then if they were in "qualified" move on to "in process" or "rejected", etc.
I'm trying to map the changes in categories in several calculation fields, and know how long it took between each changes. I'd love to have a calculation field returning the 1st category the lead was changed to (qualified or rejected), and another one for the time it took (ideally in business days but not critical). Then the same for the 2nd category the lead was changed to etc..
Some of this information can be retrieved on the activity view, but I would love to have it in the app template so I can analyse the data in bulk i.e. calculate average time, sort them by time etc.
Many thanks for your help!!
-
Hello Yacin,
not sure if I got you right and if I understand your workflow right. But maybe you can do it this way:
Create two category fields:- First Filter: "unqualified" "qualified" "rejected" (make this field required)
- Second Filter: "in process" "finally rejected"
The item is created, First Filter is automatically set to "unqualified", Second Filter is not set. In the next step of your workflow you can use Second Filter and select the needed category.
Than create four calculation fields:
First field: "First filter changed to" with code:
var firstF = @First Filter; firstF != "unqualified" ? firstF : nullThis shows the value "unqualified" changes to (could be "qualified" or "rejected"). BUT: If you first click "qualified" and than "rejected" this calculation field will also change from "qualified" to "rejected", you can't keep a history (= what has been selected first) in a calculation field. To avoid this, you should use the Second Filter for the next step of your workflow and First Filter should not be touched a second time.
Second field: "Duration first filter change"
var cOn = moment(@created on); var change = @First filter changed to != null ? moment() : ""; parseFloat(change.diff(cOn, 'm')/(60*24))This calculates the duration from the moment the item has been created (and First Filter has been set automatically to "unqualified" ) till the moment First Filter changes. " 'm')/(60*24)" calculates it exactly like 7.33 days; if you don't need it so exactly you can use " 'd')" instead. BUT: It the same as I've explained for the first calculation field; if you change First Filter a second time, the duration till the second change will be shown.
Third field: "Second Filter changed to"
var firstF = @First filter var secondF = @Second Filter; firstF == "qualified" && secondF != "" ? secondF : ""Shows the selected category in Second Filter if First Filter is "qualified".
Fourth field: "Duration second filter change"
var cOn = moment(@Created on); var secondF = @Second Filter changed to; var durFirstF = @Duration first filter change; var change = secondF != null ? moment() : null; parseFloat((change.diff(cOn, 'm')/(60*24))- durFirstF)This calculation fetches the moment if a category in the unset Second Filter is selected, calculates the duration from item creation time to the selection time and substracts the duration of the first filter change - the result is the duration between the change time of First Filter and the selection time of Second Filter.
This is one possiblity. Another one is (if you have a Podio Plus or Premium account) to use Podio Advanced Workflow and the new function "Update here" (= this item). If you want to filter later on by the fields "First filter changed to" or "Second filter changed to" I would recommend this way.
Than "First filter changed to" and "Second filter changed to" have to be category fields, not calcultion fields. This category fields have the identical categories as "First Filter" respectivly "Second Filter". The other two calculation fields for duration can stay.
You should create two Podio workflow:
First flow:
If item has been updated
and field "First Filter" changes
Filter:
Current "First Filter" equal to "unqualified" and
Future "First Filter" has a value
Action:
Update here
only fill in a value for the category field "First filter changed to": Variable "First Filter".
The other fields can stay empty.Second flow:
First flow:
If item has been updated
and field "Second Filter" changes
Filter:
Current "Second Filter" has no value and
Future "Second Filter" has a value
Action:
Update here
only fill in a value for the category field "Second filter changed to": Variable "Second Filter".
The other fields can stay empty.Another advantage of this solution is, that it really catches the first change of "First Filter" resp. "Second Filter". Cause when you e.g. change "First Filter" from "qualified" to "rejected" the flow won't be triggered.
It's your choice :)
Rainer
rg@delos-consulting.com -
Hi Rainer - many thanks for that!
You got my workflow exactly right. This is spot on!
One thing though, when I use the below in Duration First Filter Change:
var cOn = moment(@Created On);
var change = @First Filter Changed To != null ? moment() : "";
parseFloat(change.diff(cOn, 'm')/(60*24))I get an error message: 'Script error:TypeError: undefined is not a function'
-
You can try several things. Have you ever saved the template with the calculation field? If so, please look if the calculation field is number type (click the settings of the calculation field and you'll see it. If you can select decimals, it's number type). If it's not number type you have to create a new calculation field and delete the old one cause the type can't be changed.
To be sure, that the field will be saved as rnumber type you can use a simple trick: Before you enter your calculation write in the calucaltion field:
//@YOurTitlefield
1
The Slashes are important cause Podio needs a field token in a calculation but the slashes deactivate the token. So only the number 1 will be saved and you have a fixed number type field.Then copy your calculation from the old field into the new one, delete the old field. You can delete "parseFloat", it's not necessary. If you than get an error notification like "Cannot read property 'diff' of null" ignore it and save the template.
Hope that helps.
Rainer -
Many thanks Rainer - quick question:
if I only wanted the duration fields (not the "First filter changed to") ie no need to track the precise history (seeing the category on First Filter might enough if it's never updated more than once).
Could I use something like this?
var cOn = moment(@Created On);
var change = @First Filter == 'Qualified Lead'|| 'Rejected' ? moment() : "";
parseFloat(change.diff(cOn, 'm')/(60*24))In this case I basically just want to measure the time between when the lead arrived and when the First Filter was either set to Qualified or Rejected.
Many thanks again.
-
Hi Rainer,
I tried:
var cOn = moment(@Created On);
var change = @First Filter == 'Qualified Lead' || @First Filter == 'Rejected' ? moment() : "";change.diff(cOn, 'm')/(60*24);
but I still get 'Script error:TypeError: undefined is not a function' and I cannot save the template..
I have checked and it definitely is saved as number (can change the decimals).
Any idea on what to do?
Many thanks! Yacine
-
Just click on "done" to save . Does a red window pop up over the "done" button? If yes, what does it say? Maybe there is an error in another calculation field which doesn't let you save the template. Which browser do you use?
You can try also the following: Delete the calculation field and save. Create an item with values in the fields you need for this calculation, set first filter to "Qualified lead", save it and click in this item on "modify template". Than create the calculation field again.Now the calculation has values to calculate with, no error should occour and you can save it.
-
What a nasty one...
If I try to clicl on Done, the red box appears saying "Cannot read property of 'diff' null" and I simply cannot save it. If I double click I leave the modify template mode but the field is not saved..
I tried the second method but it didn't work either, unless I misunderstood - I've deleted the calculation field of the template, then created a new item, set First Filter to 'Qualified Lead', created the calculation field again like this
var cOn = moment(@Created On);
var change = @First Filter == 'Qualified Lead' || @First Filter == 'Rejected' ? moment() : null;
change.diff(cOn, 'm')/(60*24);But I still get "'Script error:TypeError:Cannot read property of 'diff' null" that I cannot ignore and save without fixing..
NB: with moment() : " "; instead of moment() : null; the error I get is 'Script error:TypeError: undefined is not a function'
Really don't know what to do...
-
Really weird. I can save even with this notification error (Chrome Windows). You seem to get "null" as result of your var change, although you have selected "Qualified Lead". Please control if the strings "Qualified Lead" are 100% identical in your calculation field and in your category field; control if there's a blank space behind "Qualified Lead" in the category.
Is the field "First Filter" a multi choice or a single choice category field?
Try this in the last line:
change.diff(cOn, 'm')/(60*24) || 0
or
change.diff(cOn, 'm')/(60*24) || nullTry also to empty your browser cache.
-
tried all this and none worked..
I wonder if this is really the issue. I wonder if there's not something wrong with the operator sign ==? I've been playing with it and when I try the below (with = instead of ==)
var cOn = moment(@Created On);
var change = @First Filter = 'Qualified'|| @First Filter = 'Rejected' ? moment() : null;
change.diff(cOn, 'm')/(60*24);I get "invalid left-hand side in assignment". But when I try without the 'OR' i.e. just 1 category option, then it works and I get a number in the preview field...
var cOn = moment(@Created On);
var change = @First Filter = 'Qualified' ? moment() : null;
change.diff(cOn, 'm')/(60*24);Any idea?
-
Hi Yacine,
The problem is really the value after moment() : . If you put in cOn instead of null or "" it works and you can save.
The result would be 0.00, because var change would be cOn and cOn.diff(cOn) = 0.00. But this not what it should be.I think this will work now for you too
var cOn = moment(@Created On); var change = @First filter == "Qualified"|| @First filter == "Rejected"? moment() : null; var result = change != null ? change.diff(cOn, 'm')/(60*24) : null resultI've inserted another IF-condition in var result: Only IF change != null than do the diff-calculation, elso show nothing.
You have to use always double equal signs or triple equal signs as operator, single won't work.
Rainer
Please sign in to leave a comment.
Comments
15 comments