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.

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!