calculate time passed between category changes + map the successive categories

Comments

15 comments

  • Rainer Grabowski (FVM)

    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 : null
    

    This 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

    0
    Comment actions Permalink
  • Yacine Ghalim

    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'

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    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

    0
    Comment actions Permalink
  • Yacine Ghalim

    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.

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    You have to repeat the if-condition after the pipes:
    var change = @First Filter == 'Qualified Lead' || @First Filter == 'Rejected' ? moment() : "";

    But as I said: You don't need parseFloat
    Rainer

    0
    Comment actions Permalink
  • Yacine Ghalim

    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

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    Hi Yacine,

    try moment() : null;instead of moment() : "";

    You'll get another error notification like "Cannot read property of 'diff' null" - ignore that and save.

    Rainer

    0
    Comment actions Permalink
  • Yacine Ghalim

    There's something weird, I indeed get the "Cannot read property of 'diff' null" but it seems like I cannot ignore and save it anyway. How do you do that?

    Many thanks - Yacine

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    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.

    0
    Comment actions Permalink
  • Yacine Ghalim

    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...

    0
    Comment actions Permalink
  • Yacine Ghalim

    (I use chrome on Mac OS)

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    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) || null

    Try also to empty your browser cache.

    0
    Comment actions Permalink
  • Yacine Ghalim

    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?

    0
    Comment actions Permalink
  • Yacine Ghalim

    (although I can change 'Qualified' for anything and I still get a number...so probably not the solution either..)

    0
    Comment actions Permalink
  • Rainer Grabowski (FVM)

    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
    result
    

    I'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

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk