Number calculated field not properly sorted

[Mislav Ugrin]

Hi, I am using a little bit more complex math expression in number calculated field and jira is not sorting by it's value. Value is being calculated properly.

[Fidel Castro Armario]

Hi @mugrin5117,

The problem is that you expression takes too long to be calculated, and JIRA withdraws from trying to sort by the field.

You can do the following 3 optimizations in your formula:

Replace term:

    (
        (modulus(floor(({00057}-{00009})/{HOUR}), 24) = 0) ? 0 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 1) ? 1 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 2) ? 2 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 3) ? 3 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 3) ? 3 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 4) ? 4 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 5) ? 5 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 6) ? 6 :
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 7) ? 7 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 8) ? 8 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 9) ? 9 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 10) ? 10 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 11) ? 11 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 12) ? 12 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 13) ? 13 :
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 14) ? 14 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 15) ? 15 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 16) ? 16 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 17) ? 17 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 18) ? 18 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 19) ? 19 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 20) ? 21 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 21) ? 23 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 22) ? 25 : 
        ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 23) ? 27 : 0))))))))))))))))))))))))
    )

with term

getMatchingValue(modulus(floor(({00057}-{00009})/{HOUR}), 24),
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23],
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 23, 25, 27])

Replace term:

    (
        ((floor(({00057}-{00009})/{DAY}) = 0) ? 0 : 
        ((floor(({00057}-{00009})/{DAY}) = 1 ) ? 5 : 
        ((floor(({00057}-{00009})/{DAY}) = 2 ) ? 8 : 
        ((floor(({00057}-{00009})/{DAY}) = 3 ) ? 10 : 
        ((floor(({00057}-{00009})/{DAY}) = 4 ) ? 11 : 
        ((floor(({00057}-{00009})/{DAY}) = 5 ) ? 12 : 
        ((floor(({00057}-{00009})/{DAY}) = 6 ) ? 12 : 
        ((floor(({00057}-{00009})/{DAY}) = 7 ) ? 12 : 
        ((floor(({00057}-{00009})/{DAY}) = 8 ) ? 13 : 
        ((floor(({00057}-{00009})/{DAY}) = 9 ) ? 13 :
        ((floor(({00057}-{00009})/{DAY}) = 10) ? 13 : 
        ((floor(({00057}-{00009})/{DAY}) = 11) ? 14 : 
        ((floor(({00057}-{00009})/{DAY}) = 12) ? 14 : 
        ((floor(({00057}-{00009})/{DAY}) = 13) ? 14 : 
        ((floor(({00057}-{00009})/{DAY}) = 14) ? 15 : 
        ((floor(({00057}-{00009})/{DAY}) = 15) ? 15 : 
        ((floor(({00057}-{00009})/{DAY}) = 16) ? 15 : 
        ((floor(({00057}-{00009})/{DAY}) = 17) ? 16 : 
        ((floor(({00057}-{00009})/{DAY}) = 18) ? 16 : 
        ((floor(({00057}-{00009})/{DAY}) = 19) ? 16 : 
        ((floor(({00057}-{00009})/{DAY}) = 20) ? 17 : 
        ((floor(({00057}-{00009})/{DAY}) = 21) ? 17 : 
        ((floor(({00057}-{00009})/{DAY}) = 22) ? 17 : 
        ((floor(({00057}-{00009})/{DAY}) = 23) ? 18 : 
        ((floor(({00057}-{00009})/{DAY}) = 24) ? 18 : 
        ((floor(({00057}-{00009})/{DAY}) = 25) ? 18 : 
        ((floor(({00057}-{00009})/{DAY}) = 26) ? 19 : 
        ((floor(({00057}-{00009})/{DAY}) = 27) ? 19 : 
        ((floor(({00057}-{00009})/{DAY}) = 28) ? 19 : 
        ((floor(({00057}-{00009})/{DAY}) = 29) ? 20 : 
        ((floor(({00057}-{00009})/{DAY}) = 30) ? 20 : 
        ((floor(({00057}-{00009})/{DAY}) = 31) ? 20 : 
        ((floor(({00057}-{00009})/{DAY}) = 32) ? 21 : 
        ((floor(({00057}-{00009})/{DAY}) = 33) ? 21 : 
        ((floor(({00057}-{00009})/{DAY}) = 34) ? 21 : 
        ((floor(({00057}-{00009})/{DAY}) >= 35) ? 22 : 22))))))))))))))))))))))))))))))))))))
    )

with term

getMatchingValue(floor(({00057}-{00009})/{DAY}),
[00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34],
[00, 05, 08, 10, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22])

Replace term:

(%{11620} = "Da") ? 3 : ((%{11620} = "Ne") ? 0 : 0)

with term

%{11620} = "Da" ? 3 : 0

Try not to use more parentheses than the necessaries, since they also take some time to be evaluated.

[Mislav Ugrin]

Hi @fcarmario Ok, getMatchingValue is a great function to use for this case. I optimised my expression as you proposed but it is still timing out.

I took out all other logic except this one, still not being sorted.

getMatchingValue(floor(({00057}-{00009})/{DAY}),
[00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34],
[00, 05, 08, 10, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22])

[Fidel Castro Armario]

Hi @mugrin5117,

Try reindexing your JIRA instance, please.

[Mislav Ugrin]

Ok, will do it and will let you know after reindexing

[Fidel Castro Armario]

Hi @mugrin5117,

Any update on this issue?

[Mislav Ugrin]

Hi @fcarmario, sorry for not answering earlier, was waiting to our sys admin to create test jira instance. We performed reindex, but still the same, sorting is not working. We have a quite large jira instance, more then 1300000 tickets, maybe this is making performance problems?

[Fidel Castro Armario]

Hi @mugrin5117,

I'm closing this issue. Please, reopen it if you think I can help you on this. Thanks.