Hi there,
we have a Jira dataset with 2 date columns Date_Created and Date_Resolved. We want to create at line graph that will represent in the X-axis a calendar date and 2 lines that will show tickets created and resolved in that calendar date. Something like this (red: created, green: resolved):
Keep in mind that we don’t have access to SQL here since it’s a Jira data source.
Thank you all!
Hello @daniel.lazaro - Thank you for posting your query. On a quick peek at your query, I believe if you can afford to upload a Date Dimension Dataset then you can use it as master dataset to perhaps get this done.
Step 1 : Upload the Date dimension dataset which will have all calendar date listed out covering your analysis years both in the past and future. This is a typical data dimension dataset which will have all date dimension related columns present to faciliatate your slicing and dicing.
Step 2 : Create 2 datasets from your Jira Source Dataset - one with the Created Date present in it (naming the dataset as Created) and other Resolved Date present in it (referring it as Resolved)
Step 3 : Establish Join such as Master Left Outer Join Created & Master Left Outer Join Resolved
Step 4 : Create Line graph out of the resultant dataset by plotting the Date column from the Master Dataset Column list and then project the Tickets (with Count as aggregate) one by one from Created Dataset Column and Resolved Dataset column to get your desired visual.
Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
This worked @sagmukhe ! Thank you so much.
I think it would be a great feature if we could have a detached calendar dimension in Quicksight (as you can in Power BI).
In this case, I had to import twice the already created dataset with 2 calculated columns that truncated the time out of the ticket datetimes.
@daniel.lazaro - Glad to know that it worked. I agree with you. QuickSight currently doesn’t provide us any option to model the data like what Power BI allows to establish relationships among tables keeping them detached. However, I know that they have plans to introduce “Multiple Table Support”. Having said this, we don’t have visibility when this feature would be introduced. You can keep an eye on the upcoming announcements to see what’s getting added into QuickSight here → What's New / Blog - Amazon QuickSight Community
