Replicating Jira graph in Qlikview - Open/Closed tickets per day

[lukegilligan]

I am trying to replicate the following graph that is on our Jira service desk in Qlikview:

I have been able to get the data in to QV with no issues, but I am having a problem counting the number of resolved issues per day due to the fact that I am using the Created Date as the dimension, which is an issue due to the fact that there are tickets that are resolved on dates where new tickets weren't created.

I presume I will need to use some sort of master calendar to get this working, but am getting a bit stuck how to generate the extra dates to allow the resolved dates to show.

Attached is a QVD of where I have got to so far.

Any help would be much appreciated.

Luke

[Claudiu_Anghelescu · Accepted solution]

You need a common Dimension for Creation.Date and Resolved.Date.

JiraDataCreated:
LOAD 
......
FROM ....;

NoConcatenate
Table:
LOAD *,
'C' as Flag,
Created.Date as Date
Resident JiraDataCreated
WHERE Created.Date <> '';

Concatenate

LOAD *,
'R' as Flag,
Resolved.Date as Date
Resident JiraDataCreated
WHERE Resolved.Date <> '';

Drop Table JiraDataCreated;

[Brett_Bleess]

Here is link to a Design Blog post regarding Master Calendar, there are some other Calendar posts out there as well, just FYI, hopefully this may help further.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Regards,
Brett

[lukegilligan]

Thanks this is exactly what I was trying to achieve.