Hi there,
I have a report showing hours spent per ticket with labels referring to each Quarter of the year.
So, we have tickets which can have one or more of these labels: Q1.2023, Q2.2023, Q3.2023 and Q4.2023.
I already built a report where I can see, per sprint, how many hours we spent on tickets with each label. The “problem” is that some tickets were planned for Q1 but they have been done in Q2, so they have more than 1 Quarter label. In those cases, they appear as duplicates in my report.
How can I show the hours spent only referring to the latter label? If it has Q1, Q2 and Q3 labels, I’d like to to count towards Q3 only. Is this possible? How would that sorting look like? Is it alphabetical, is it by label which was added the latest?
Or can it somehow figure out that the ticket was closed in the date which belongs to Q3 and count towards that label with the same name?
We could also allow the sorting to be done alphabetically, but if we do this we’d have to restrict this to only catch labels starting with “QX” (i.e starting with “Q1.” or “Q2.” or “Q3.” or “Q4.” )
I also created a calculated member called “No Quarter Label” as I want to see how much time we spend with tickets which weren’t planned for the Quarter. Here I basically subtract all Quarter labels. It looks like this but I’m sure it could be somehow improved:
([Label].[All Labels]- [Label].[Q2.2023]- [Label].[Q1.2023]-
[Label].[Q3.2023])
Let me know if I should explain something a little better, I hope it wasn’t too confusing.
Best regards and thanks in advance!
Hi @bernardo.c
Welcome to the Community 
In this case, I would recommend creating a new JavaScript calculated custom field to retrieve the latest quarter label for each issue.
Head over to your Advanced settings and use the following lines of code to define this custom field:
[jira.customfield_qlabel]
name = "Quarter label"
data_type = "string"
dimension = true
javascript_code = '''
var labels = issue.fields.labels;
if (labels && labels.length > 0) {
let labelName = "other labels";
let labelValue = 0;
for (let i = 0; i < labels.length; i++) {
let label = labels[i];
let tempLabel = label.match(/^Q([1-4]).*([0-9]{2})$/);
if (tempLabel) {
tempValue = parseInt(tempLabel[2] + tempLabel[1]);
if (tempValue > labelValue) {
labelName = tempLabel[0];
labelValue = tempValue;
}
}
}
issue.fields.customfield_qlabel = labelName;
} else {
issue.fields.customfield_qlabel = "no label";
}
'''
Save the Advanced settings, go to the “Source Data” tab, click “Edit” on your Jira source and in the “Custom fields” section find and select this newly created “Quarter label” to be imported into the account, and start the import.
After the import finishes, you can use this Quarter label dimension to sort all the issues by their latest quarterly label.
If the issue has other labels and no quarterly label, then it will fall into the “other labels” category. If the issue does not have any labels, then it will be in the “no label” category.
Let me know if this works as expected!
Best regards,
Nauris / eazyBI support
Hi @nauris.malitis thank you so much for taking the time to help out!
Your solution is great and appears to be working as intended. Thank you so much for that!
I do have one or two more tweaks I’d like to ask of you.
-
Can we dig into a bit more detail on the “other labels” part?
We have another label - let’s call it “ieffects” for now - which I also want to put in this Quarter label view. As a note, no ticket with label “ieffects” will ever have a Quarter label.
And maybe in the future we might also have another label called “Technical Tasks”. How would I go about adding these 2 and maybe further labels to this detailed view?
-
Smaller question, will this also work for Q1.2024 for example?
Thank you!
Hi @bernardo.c
Happy to hear it’s working as intended!
To answer your questions:
- You can add additional if statements to check the label values in the for loop, the code could be along these lines:
[jira.customfield_qlabel]
name = "Quarter label"
data_type = "string"
dimension = true
javascript_code = '''
var labels = issue.fields.labels;
if (labels && labels.length > 0) {
let labelName = "other labels";
let labelValue = 0;
for (let i = 0; i < labels.length; i++) {
let label = labels[i];
if (label == "ieffects") {
labelName = "ieffects";
break;
} else if (label == "Technical Tasks") {
labelName = "Technical Tasks";
break;
} else {
let tempLabel = label.match(/^Q([1-4]).*([0-9]{2})$/);
if (tempLabel) {
tempValue = parseInt(tempLabel[2] + tempLabel[1]);
if (tempValue > labelValue) {
labelName = tempLabel[0];
labelValue = tempValue;
}
}
}
}
issue.fields.customfield_qlabel = labelName;
} else {
issue.fields.customfield_qlabel = "no label";
}
'''
If you’ll have any other labels added that you would like to separate out, just add another “else if” statement after the Technical Tasks:
} else if (label == "Technical Tasks") {
labelName = "Technical Tasks";
break;
} else if (label == "some other label") {
labelName = "some other label";
break;
} else {
...
- The quarter label compare function is built to create a number from the last two digits of the year and the quarter number, so for Q3.2023 it will be 233, and for Q1.2024 it will be 241, so it will work correctly until the year 2100
Best regards,
Nauris
Hi @nauris.malitis thanks for your reply. Unfortunately the “ieffects” label is not showing correctly, it appears as empty in the report, although I see some issues with that label in the “other labels” column. Would you be so kind and have a quick look, please? Many thanks
Hi @nauris.malitis, quick side note that we just closed a Sprint and now that we have that new Sprint in the report, I see correct data in the “ieffects” label for that report only. Not sure why it’s still not showing “ieffects” data in the previous sprints though.
Hi @bernardo.c
Apologies for the delay!
Please try to perform a double-import of this “Quarter label” custom field to refresh the data after the changes made in the Javascript code:
- Go to the “Source Data” → “Edit” → “Custom fields” section, deselect the “Quarter label” custom field and run the import without this field.
- After the import finishes, again, select this field to be imported and run the import.
Let me know if after the double-import the data shows up as expected!
Best regards,
Nauris
No worries at all @nauris.malitis, please take your time. Thanks for your solution, the double-import fixed the issue. I now see label “ieffects” in all previous Sprints as well. Thank you so much for your help have a nice day!
1 Like