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

    Sum Distinct?

    Chrissy Scott

      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
      • Hours Degraded

       

      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?
          Rakesh Prusty

          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?
            Chrissy Scott

            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?
              Mark Fraser

              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?
                Chrissy Scott

                Hi Mark,

                 

                That's exactly what I needed. Thank you!