4 Replies Latest reply on Mar 2, 2017 11:44 PM by Mahfooj Khan

# Calculate average time per root cause

Hi there,

I am currently doing a root cause analysis of tickets and their effective root causes.

One ticket can have multiple root cause and hence ticket id appear multiple times compared to root causes linked to it but will have the same time taken.

I am trying to find the time taken by each ticket id divide by frequency of occurrence of ticket ID.

Some sample data

 ID Time Root cause w1 30 RC1 w1 30 RC2 w1 30 RC3 w2 30 RC1 w2 30 RC2 w3 10 RC3

So simply i need RC1 have time 10 hours for w1, 15 for w2 ; RC2 have time 10 hours for w1 and 15 for w2 and RC3 have 10 hours for w1 and 10 for w3.

I am thinking that the answer is very straight forward but couldn't think of it. I am getting aggregate and non aggregate cannot be combined error when creating a calculated field.

• ###### 1. Re: Calculate average time per root cause

Hi Parimal,

If you're looking for this, try the below calculations:

[count]

{FIXED [ID]:COUNT([ID])}

[Value]

[Time]/[Count]

Thanks,

Gourav

1 of 1 people found this helpful
• ###### 2. Re: Calculate average time per root cause

Hi Parimal,

Try this,

[Time]/{FIXED [ID]:COUNTD([Root cause])}

Let us know If this help.

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Calculate average time per root cause

"Fixed" LOD calculation was the answer to my issue.

Thanks Gourav and Mahfooj

• ###### 4. Re: Calculate average time per root cause

You're welcome. Its a good practice to close the thread once you get the answer. To close the thread you can go to action of your post and mark correct the most relevant solution.

Mahfooj