Simple problem, complicated solution

[Dusan Vuckovic]

Hi, 

need to be honest here. Analytics is not up to the task. If you compare it to it's competitors like Zendesk Explore or GoodData it is way behind. 

First downside is that is built by DB experts for DB experts (thus the difference  with Explore and GoodData). It is very complicated to get what you need. A simple pivot table for instance.

Outer, inner joins this is all DB stuff which I was successfully avoiding so far, but now it's catching up with me. 

Anyway, problem I have is simple. Every ticket has a project designator (custom field ProjectID). Every Project ID has multiple tickets. I need cumulative "time spent" on every project (not a ticket, but ProjectID) and then to see that broken down into monthly time spent. How do I do this ? Check the screenshot of the same excel report please. First column is ProjectID. 

I can't beat the feeling I'm missing elements to build this. 

Thank you 

[Inder Singh]

Hi @Dusan Vuckovic

Thanks for the feedback. We understand that Analytics has a bit of a learning curve for new users, and so we are always open to suggestions. If there are ways you feel we can help you learn Analytics better, do let us know.

Coming to your problem here, I have a couple of questions:

1. Is "ProjectID" you mentioned a custom Jira field? ("Project" table also has "ProjectID" so wanted to confirm)

2. Is "time spent" the time logged in by the user (as captured in Worklog data) or is it time in status for the issue? If it is related to work log data, then this data is not yet available in Data Lake. We do have a ticket you can follow you get updates https://jira.atlassian.com/browse/ANALYTICS-22. As a workaround, you can try the following:

The existing Jira Issue History table contains data on when the time tracking value was changed on an issue. You can find this information by filtering on field="timespent". Some of the data that it contains include:

• User ID of the user who logged in the time spent
• Time spent that was logged by the user
• The time when the log was created

Cheers,

Inder

[Dusan Vuckovic]

Hi, 

Thank you Inder for the answer. I am available to your Analytics team (already had some meetings with them in regards to Data lake improvements), so be free to reach out since I have a lot of experience with these competitive products (from the user perspective) . 

Anyway, Project ID isa custom field. it is not related to your system "Project" . 

Also, I was talking about worklog data. Your Catch-22 workaround is producing results, but how do I accumulate them under ProjectID and present in monthly splits? 

Thank you very much

[Matthew Lempitsky]

Hi Dusan,

I do not have a custom field called "Project ID" to mock up an example with, so I used a different custom field called "Priority", you can use the same approach, but instead use your project ID field.

You can see I can accomplish this with a single visual SQL query and then a few follow up steps. 

I hope this helps!

[Dusan Vuckovic]

OK. Much appreciated . Let me see if I can replicate. 

Ty

[Dusan Vuckovic]

Any chance you could show me how your group and aggregation step looks like ? I'm getting results but I also have duplicated values for certain months

This should be one line for 2024-03 accumulated. Ty

[Matthew Lempitsky]

Hi Dusan, 

I believe your 5th column is what is causing the duplicate rows. You want to change that aggregation to SUM (or something other than group) so that they rows are combined. 

If you run into any more issues with this, feel free to open a support ticket and we can continue the conversation, in depth, there. 

[Dusan Vuckovic]

Will do. Thank you very much