4 Replies Latest reply on Jan 14, 2016 1:32 PM by Chrissy Scott

# Sum Distinct?

Hi All,

Is there a way to get a SUM DISTINCT calculation?

I have the following data points:

• Tickets - includes duplicates of individual Call Numbers
• CountD(Call Number) - This is a calculated field to give me the count of individual ticket numbers

My problem is this: The Hours Degraded field is summing the hours for the duplicates which is inflating total numbers. Basically if there is 1 ticket/call number that affected 3 people, each for 1 hour the Hours Degraded field is giving me 3 hours because that ticket/call number is listed 3 times. I need it to give me 1 hour.

So I need to get the sum of Degraded Hours for CountD(Call Number). I need that to generate an accurate MTTR.

Sample data is attached.

Thoughts?

• ###### 1. Re: Sum Distinct?

Hi Chrissy!

I am not sure if I understood the question clearly but I tried to solve the problem based on what I understood.

You were trying to sum the degraded hours for the distinct call numbers. I think this can be solved by LOD expression as below.

{FIXED [Call Number]:SUM([Hours Degraded 2])}

However, There is no duplicate call number present in your data. So, the result does not make difference. The file is attached. Please let me know if this helps or I misunderstood the question.

• ###### 2. Re: Sum Distinct?

Hi Rakesh,

Thanks for responding.

The duplicate data is in Call Number. On the data source tab, the first column... you'll need to scroll down a bit to see the first duplicate (211978). So for example, for FY15Q2:

• The total count of Tickets = 179. That's because some tickets/call numbers may affect multiple users and are being counted 2-3 times each.
• The total UNIQUE count of Tickets/Call Numbers = 129
• I need the total hours degraded in those 129 tickets

So say there are 10 UNIQUE tickets, each causing 1 hour degraded. And 1 of those affected 2 users.

• Right now my set up is saying that's 11 degraded hours over 11 tickets (at least that's what I think is happening...)
• I need it to say 10 hours degraded over by 10 tickets

Does that make more sense?

• ###### 3. Re: Sum Distinct?

Hi Chrissy

To check - Ticket 2117978 should return 0.8? (rather than 1.6)

The correct MTTR for FY15Q2 is 2.13?

Assuming so, I think Rakesh was close, but instead of SUM (which is continuing the problem of adding the 'duplicate' degraded hours, you need to MIN instead.

Calc1 = {FIXED [Call Number] : MIN([Hours Degraded 2])}

MTTR then becomes AVG([Calc1])

This is my output Cheers

Mark

• ###### 4. Re: Sum Distinct?

Hi Mark,

That's exactly what I needed. Thank you!