Forum Discussion
Cumulative backlog to date
andrew_hardwick Couple small changes since there are blank dates... But you should be set now! Let me know.
Thats great. thanks for that, I now have a Calendar. I have built my report using the measures you stated and the Calendar and it is now almost there, I have the balance and RT Balance showing. The ony remaining issue I see is that some of the lines do not yet have a resolved date, they are still being worked on. This therefore gives me a negative RT Balance at the start as their is no date assigned. Instead I would like this open entries to appear on the latest RT Balance day, i.e. today.
Any idea how I could achieve this?
andrew_hardwick Couple small changes since there are blank dates... But you should be set now! Let me know.
Thats great Sean, thanks for your help :-)
Hi guys, using the opened date from the tickets table everything looks awesome, but using the dates from the date table I got this behavior about to get the negative values at the beginning of the table. Any suggestion?
diegoadum I'm not entirely sure what your issue is, can you elaborate and I will see if I can help?
Hi andrew_hardwick, I have solved the issue. it was related to the data type using on the dates fileds in my model. Since I use a master calendar (follow your suggestion) to link with my "tickets" table, and my tickets table has 2 dates fields (Open Date and Close date) I found a situation related to the "close date" field. Some of the tickets are Cancelled, therefore there is no close date in the table, so I have to create a calculate field using the State of each record to figure out if the ticket was cancelled, if so, I'm using Updated date isntead. But, what this cause is that for some reason, I have to change the data type for all the dates field in order to match them as Date (taking away the time portion). At the end and having the same data type for all the date field involve on this the model is working. Hope the explanation can't cause more confution!!
Hello all,
The calculation for the backlog worked perfectly. However, I would like to display the sum of "points" assigned to each ticket instead of the count of the opened tickets, closed tickets, and RT balance.
Ticket Number Opened Date Closed Date Points
1234 01-01-2019 02-01-2019 7
1254 01-02-2019 01-09-2019 3
5432 01-31-2019 02-01-2019 1
1456 01-22-2019 02-08-2019 4
So I would like to display the sum of points for the opened, sum of points for the closed, and sum of points for the backlog.
Thank you in advance!
Hey Sean , thanks a lot! I found this very helpful. I know the post is kind of old, but it's gold.
And I also have a question, I needed the RT Balance, and I also need to find the closed balance from previous periods. For example, this month I resolved three tickets that where created on previous months, I need to show that in the chart along the RT Balance, how would I write a measure for that? I have tried several things, but nothing seems to be working.
Any advice or guidance in the right direction would be appreciated. Thanks a lot!
Hi Sean & andrew_hardwick ,
I have a similar requirement to produce a running total backlog at the end of each week and I think the key is to get the values for each day as illustrated in this example. In my model, a table with dates and other Org specific values already exist and the Date (Ticket Created) field in the master table is linked to the Date field in the Dates table. However, I am having an issue with linking both Date (Ticket Created) and Date (Ticket Solved) to the Date field in the Date table as the relationship for Date (Ticket Solved) remains inactive. I get the message 'You can't create a direct active relationship between Table A and Date Table because an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter direction to "single", delete or deactivate any of the indirect relationships first.".
Once I get past this, I need some help in segregating the backlog of tickets based on ticket type which can be one of "incident", "problem", "question", "task" and by priority which will be one of "urgent", "high", "normal", "low". All tickets being counted as backlog should be out of SLA for which I am using the SLA metric assigned by the tooling solution and have created a field IsSLABreach = True / False. So for my requirement, IsSLABreach should always be true.
Kindly help and oblige.
