calculating child issues time

[rTrack Support]

Hi @Fidel Castro Armario ,

I need to calcualte the total time taken on each child issues and need to display it in the parent issue .

For ex : 

Parent issue 1 

child issue 1 

child issue 2 

child issue 3 

These child issues are created using creates issues and subtask post functions .

in parent issues : open --> in progress --> resolved .

need to take the difference b/w resolved and created .

in the same parent issues :

open --> inprogress --> resolved --> rejected --> in progress --> resolved .

in this scenario , i dont need to take the difference between created and resolved .

i copied the transition time when it is resolved to rejected as Reopened Time .

Once again i changed to resolved Then resolved field will updated with the current time .

So i have to check the scenarios :

if Reopened date is null then need to take differenece b/w created and resolved .

else reopened date is not null then need to take the difference b/w  reopened date and resolved .

[Fidel Castro · Accepted solution]

If you also want a field in each child issue, you can create a "Calculated Text Field" with the following text expression:

formatDuration({rrrrr} != null ? {ccccc} - {rrrrr} : {ccccc} - {00009})

replacing rrrrr with field code for Final Reopen Date, and ccccc with field code for Final Closure Date.

Note that {00009} is field code for Date and time of creation.

[Fidel Castro]

You can create a "Calculated Text Field" in parent issue with the following text expression:

formatDuration(sum(mathOnIssueList(linkedIssues("initiates (Issue originated from another Issue)"), ^{rrrrr} != null ? ^{ccccc} - ^{rrrrr} : ^{ccccc} - ^{00009})))

replacing rrrrr with field code for Final Reopen Date, and ccccc with field code for Final Closure Date.

Note that {00009} is field code for Date and time of creation.

[rTrack Support]

The duration will be in the format of time

or

like 2days 4 hrs 30 minutes .

And also .If i set the due date is with certain conditions once resolved the issues , i have to take the difference between resolved and due date .

if resolved date is greater than due date then need to set a field as SLA OUT .

else resolved date is lesser than due date then need to set a field with value as SLA IN

[Fidel Castro]

The format is like 2days 4 hrs 30 minutes.

[Fidel Castro]

I recommend you to create a Text Field (single line) custom field and set it in transition for resolving issue using "Copy a parsed text to a field" post-function with the following configuration:

• Target field: the Text Field (single line) you have created.
• Parsing mode: advanced
• Text to be parsed:

formatDuration({00012} - {ccccc}) + " - " + ({00012} - {ccccc} > 0 ? "SLA IN" : "SLA OUT")

replacing ccccc with field code for Final Closure Date.

Note that {00012} is field code for Due date.

[rTrack Support]

Can i use the single level select custom field instead of the text field .

. And one more thing .

I have tried the calculated number field for taking the counts on transitions .

It results the number .

But i cant see that custom field in the JIRA dashboards like pie charts , two dimensional custom fields .

[Fidel Castro]

You can use a Text Field (single line) for the time from resolution to due date with the following configuration:

• Target field: the Text Field (single line) you have created.
• Parsing mode: advanced
• Text to be parsed:

formatDuration({00012} - {ccccc})

replacing ccccc with field code for Final Closure Date.

Then use a Single Select List custom field for SLA result with the following configuration:

• Target field: the Single Select List you have created.
• Parsing mode: advanced
• Text to be parsed:

{00012} - {ccccc} >= 0 ? "SLA IN" : "SLA OUT"

replacing ccccc with field code for Final Closure Date.

[rTrack Support]

@Fidel Castro Armario

 And one more thing .

I have tried the calculated number field for taking the counts on transitions .

It results the number .

But i cant see that custom field in the JIRA dashboards like pie charts , two dimensional custom fields .

Could you please check this ..

[Fidel Castro]

Numeric fields can't be shown in a pie chart or 2 dimensional table. You can't also add an ordinary number custom field to those 2 dashboard gadgets.

[rTrack Support]

Will text field supports ??

[Fidel Castro]

No, only fields with a limited number of possible values, like Select List, User Pickers, Project Pickers, Issue Pickers, etc.

[rTrack Support]

Ok @Fidel Castro Armario .

Thanks for the valuable suggestions .

Is there any possibilities to include the holidays in the conditions of setting the due date .

For example today here is leave for the onsite need to be leave based on their holidays .

Is it possible to set based on the reporters geo location or regions .

[{Due date}=null AND %{Severity}="Sev1-Show Stopper"]addTimeSkippingWeekends({Date and time of creation}, toNumber(projectProperty("severity1Hours")) * {HOUR} + 1, EST)

[{Due date}=null AND %{Severity}="Sev2-Critical"]addTimeSkippingWeekends({Date and time of creation}, toNumber(projectProperty("severity2Days")) * {DAY} + 1, EST)

[{Due date}=null AND %{Severity}="Sev3-Not Critical"]addTimeSkippingWeekends({Date and time of creation}, toNumber(projectProperty("severity3Days")) * {DAY} + 1, EST)

[{Due date}=null AND %{Severity}="Sev4-Trivial"]addTimeSkippingWeekends({Date and time of creation}, toNumber(projectProperty("severity4Days")) * {DAY} + 1, EST)

[rTrack Support]

this is for a single project only i need based on the reporter places 

[Fidel Castro]

Sorry, currently it's not possible to use a holiday calendar for doing time calculations. That's a feature that will be added in the future, but it will not take short time to be implemented.

[rTrack Support]

Yah sure .. Try to implement the calendar logics .That will reduce too much complexities for the customers .

And i have a general questions .

I am configuring too much automation's between 4 to 5 queues with lots of post functions .

Will it result any damage for the server or will it result the suspended process in the back end .

Because i am facing issues like getting locked process while doing the transitions .

[Fidel Castro]

When correctly configured, you can use a lot of post-functions in your workflows without significative time penalty. But, there are also inefficient configurations that can result in performance penalties. Nevertheless, they can usually be optimized.

Please, create an issue at http://support.workflowarts.com for the transitions you are suffering from long times of execution. Attach the XML export of the workflow and explain which transitions are being troublesome.

[rTrack Support]

Yah sure fidel .Thanks for the immediate responses at every time .

[rTrack Support]

@Fidel Castro Armario,

I have one doubt .

I used the scenario using the properties of severity .

It is working fine .

But while showing the when the time is completed with in sla 

then it shows the time and also as SLA IN .

But in the time i have already used skipping weekends .

So in this field also i have to subtract the weekend values also right ??

Is it possible ??

[rTrack Support]

@Fidel Castro Armario

And also I have another one requirements .

i have the some status flows like below for one queue .

open --> in progress (Named as T1)

in progress --> waiting for vendor (Named as T2 )

waiting for vendor --> in progress (Named as T3)

 in progress --> resolved (Named as T4)

Now i have to calculate the total time taken to resolve the issue with below condition .

if i changed the status from in progress to waiting for vendor Then i have to add the time from 

open --> in progress 

and 

in progress --> waiting for vendor 

and then again i need to take only the time 

in progress --> resolved .

i.e ( T1 + T2 +T4) = Total time 

i need to eliminate the time T3 .

And also i have to check with the business hours also .

is it possible ???

[Fidel Castro]

Sorry, I'm not sure about what you want. If you want to do the calculations skepping weekends, replace term {00012} - {ccccc} in all your expressions with this one:

subtractDatesSkippingWeekends({00012}, {ccccc}, LOCAL)

[Fidel Castro]

Please, create a new question for your new requirement. Thanks.

[rTrack Support]

@Fidel Castro Armario ,

I have used this based on your suggestions  .

Ex :

formatDuration({00012} - {ccccc}) + " - " + ({00012} - {ccccc} > 0 ? "SLA IN" : "SLA OUT")

Conflgured :

1. The following text parsed in advanced mode will be copied to Sla Status: 
   formatDuration({Due date} - {Final Closure Date}) + " - " + ({Due date} - {Final Closure Date} > 0 ? "SLA IN" : "SLA OUT")
   This feature will be run as user in field Current user. 
   by JWT
   But it shows result correctly for sla in values . For sla out it is showing like :

image2017-2-21 10:30:44.png

Can you please suggest us for the solution .

[Fidel Castro]

Try any of these alternative expressions:

Not showing duration for SLA OUT:

{00012} - {ccccc} > 0 ? formatDuration({00012} - {ccccc}) + " - SLA IN" : "SLA OUT"

Showing duration for SLA OUT:

{00012} - {ccccc} > 0 ? formatDuration({00012} - {ccccc}) + " - SLA IN" : formatDuration({ccccc} - {00012}) + " - SLA OUT"

[rTrack Support]

@Fidel Castro Armario

Is this correct ???

{00012} - {ccccc} > 0 ? formatDuration(subtractDatesSkippingWeekends({00012}, {ccccc}, LOCAL)) + " - SLA IN" : formatDuration(subtractDatesSkippingWeekends({ccccc}, {00012}, LOCAL)) + " - SLA OUT"

I have to display the days only t business days for sla in and out values . i used to skip the weekends while adding the due dates .

[Fidel Castro]

Yes, that's correct.

[rTrack Support]

@Fidel Castro Armario ,

hi fidel ,

is this is a valid condition :

{00012} = null ? ({rrrrr} = null ? ({created} - {12156} > 0 ? "SLA IN" : "SLA OUT") :({rrrrr} - {12156} > 0 ? "SLA IN" : "SLA OUT") : ({00012} - {12156} > 0 ? "SLA IN" : "SLA OUT")

[Fidel Castro]

That's not a condition but a math expression that contains conditional clauses.

You should use {00009} instead of {created}. Anyway, your expression is not correct because correct syntax is:

 boolean_expression ? if_math_expression : else_math_expression

but you are using something like:

boolean_expression ? math_expression : math_expression : math_expression

which is incorrect.

Also {rrrrrr} should be replaced by an actual field code.

Please, explain in detail what you intend to do and I will give you the exact expression.

[rTrack Support]

@Fidel Castro Armario,

I am trying to check this

if due date is null then 

      if Reopen date is null then

                  created date - closed date

      else reopen date -closed date 

Else

due date - closed date .

Boolean expression ?( Boolean ? Math :math ) : math 

I am trying to achieve the above sub condition check .

simple if block (if block then else )

else block .

[Fidel Castro]

Use the following string expression:

{00012} = null ? ({rrrrr} = null ? ({00009} - {ccccc} > 0 ? "SLA IN" : "SLA OUT") : ({rrrrr} - {ccccc} > 0 ? "SLA IN" : "SLA OUT")) : ({00012} - {ccccc} > 0 ? "SLA IN" : "SLA OUT")

replacing rrrrr with field code for Reopened Date and ccccc with field code for Closing date.

[rTrack Support]

@Fidel Castro Armario

Shall i use this as condition .

00012 – due date 

19691 – reopen date

closure date – 19692

00009 – created date

{00012} =  null ? ({19691} = null ? ({00009} - {19692} > 0 ? (formatDuration(subtractDatesSkippingWeekends({00009}, {19692}, LOCAL)) + " - SLA IN") : (formatDuration(subtractDatesSkippingWeekends({19692}, {00009}, LOCAL)) + " - SLA OUT")) : ({19691} - {19692} > 0 ? (formatDuration(subtractDatesSkippingWeekends({19691}, {19692}, LOCAL)) + " - SLA IN") : (formatDuration(subtractDatesSkippingWeekends({19692}, {19691}, LOCAL)) + " - SLA OUT") ) ) : ({00012} - {19692} > 0 ? (formatDuration(subtractDatesSkippingWeekends({00012}, {19692}, LOCAL)) + " - SLA IN") :(formatDuration(subtractDatesSkippingWeekends({19692}, {00012}, LOCAL)) + " - SLA OUT")) 

[Fidel Castro]

That expression is syntactically correct, and equivalent to the one I provided you before but doing calculations skipping weekends. So, it should work as you expect.

Anyway, thinking of your description of your requirements, there is something I don't understand:

When due date is null the time intervals to be calculated are created date - closed date or reopen date -closed date, but we are comparing it with zero. Those values will always be negative, since closed date will always be greater than created and reopened dates. I think that the comparison should be with some maximum duration (SLA time limit), i.e, the expressions should be:

{00012} = null ? ({rrrrr} = null ? ({00009} - {ccccc} > sla_duration_limit ? "SLA IN" : "SLA OUT") : ({rrrrr} - {ccccc} > sla_duration_limit ? "SLA IN" : "SLA OUT")) : ({00012} - {ccccc} > 0 ? "SLA IN" : "SLA OUT")

or

{00012} =  null ? ({19691} = null ? ({00009} - {19692} > sla_duration_limit ? (formatDuration(subtractDatesSkippingWeekends({00009}, {19692}, LOCAL)) + " - SLA IN") : (formatDuration(subtractDatesSkippingWeekends({19692}, {00009}, LOCAL)) + " - SLA OUT")) : ({19691} - {19692} > sla_duration_limit ? (formatDuration(subtractDatesSkippingWeekends({19691}, {19692}, LOCAL)) + " - SLA IN") : (formatDuration(subtractDatesSkippingWeekends({19692}, {19691}, LOCAL)) + " - SLA OUT") ) ) : ({00012} - {19692} > 0 ? (formatDuration(subtractDatesSkippingWeekends({00012}, {19692}, LOCAL)) + " - SLA IN") :(formatDuration(subtractDatesSkippingWeekends({19692}, {00012}, LOCAL)) + " - SLA OUT"))

sla_duration_limit is a duration expression like:

2 * {DAY} + 12 * {HOUR} + 30 * {MINUTE}

[rTrack Support]

@Fidel Castro Armario

my requirement is :

if due date is null

then need to check

reopen date is null or not 

if reopen date is null then need to take created vs closed else reopen vs closed 

else 

due date vs closed date .

[rTrack Support]

The sla duration limit is not necessary i think . 

when the difference b/w two dates is greater than 0 then need to show the timings only ..

i think no limits required .

limits are mandatory or not .

[rTrack Support]

@Fidel Castro Armario ,

I have compared both the expression , both are same except but 0 & sla_duration_limit

.

[Fidel Castro]

But I think there is something that is not OK with your expression, since I'm afraid that created date - closed date will always be lower than zero, and the same should be true for reopen date -closed date. I'm I wrong?

[rTrack Support]

Yah i accept this .

when i use created vs closed  then it should be less than zero .This will not occur all instances .for older issues it may occur . To avoid the error only i used this expressions .

reopen date vs closed date also need to check like this only .

[rTrack Support]

Final Reopen Date  – This date field will capture the reopen activity date time (Status : Reopened)

Final Closure Date  - this date field will capture the close activity date time  (Status : Resolved)

[Fidel Castro]

Do you have a Date-Time Picker custom field for storing the time when an issue is reopened? In affirmative case, what's the name of that field?

[rTrack Support]

so once calculated this time i have to display those times in the parent issues and also need to sum up the all field of calculated times and display in some other fields .