3 Replies Latest reply on Mar 20, 2014 5:36 AM by Joshua Milligan

    Sum of distinct Values

    Liliawati Sukamto

      I have been struggling with a problem for over 2 days and gone through the forums for quite sometime without any success. What I am trying to achieve is in the attached excel along with the raw data. What I am tying to achieve is SUM(DISTINCT(VALUES)). I am open to even changing the data structure if it means being able to achieve the end objective.

       

      Thanks in advance.

        • 1. Re: Sum of distinct Values
          Joshua Milligan

          Liliawati,

           

          How about the attached workbook?  I've made some assumptions but seem to be getting the same answers you are wanting.

           

          Here's the basic idea:

           

          1.png

           

          Since the Min of Total Base gives the distinct value, I used WINDOW_SUM(MIN([Total Base])) to sum up all the minimum values.  I did the same thing with the count and then created another calculation to divide and get % Churn.

           

          You'll notice I have a lot of duplicate rows (I have to have Sub Type in the view, but I don't want to show a row for each subtype).  So, I can hide the unwanted rows by using a filter on the calculated field First().

           

          Here's how I cleaned up the view

          2.png

          In a production environment, I'd change the window calculations to include:

          IF FIRST() == 0

          THEN {Calculation here}

          END

           

          That would make it so the calculation would only be done one time (for the row I am showing).

           

           

          Please take a look and see if it matches what you want and I'd be happy to answer any questions you might have.

           

          Regards,

          Joshua

          • 2. Re: Sum of distinct Values
            Liliawati Sukamto

            Thanks a ton Joshua. This works perfectly for me. I must add that, I just blindly followed your instructions and voila!! it worked. Now understanding it properly and internalizing it is next on my agenda.

             

            Thanks again!!

            • 3. Re: Sum of distinct Values
              Joshua Milligan

              You're welcome!  Definitely let me know if you have any quesitons.

              -Joshua