3 Replies Latest reply on Mar 8, 2016 1:49 PM by Derrick Austin

    Understanding LOD Expressions and SUM Distinct

    Rahul Ahluwalia

      Hello!

       

      I'm trying to do a Sum distinct and was able to accomplish most of what I wanted thanks to Jonathan's answer here. However, I'm running into a few issues.

       

      Let's imagine I have a data set of 20 rows. In my amount column, the value is $20,000 repeated for each row (hence, the sum of this would equal $400,000). I should also note that each row in this case has a Probability = 0. I was able to do a sum distinct as follows:

       

      { fixed [Account Id], [Probability], [Contract Type],

      [Recognition Date]:max([Last_Year_EOY_CV])}

       

      When I do a sum on that field, I receive the correct answer: $20,000.

       

      Now - I need to repeat that calculation, but only when the probability is = 0. Now it really shouldn't matter which rows have a probability = 0 since $20,000 is repeated for each row; thus, the answer should still be $20,000 as long as at least 1 row has probability = 0. To try and accomplish this, I did the following:

       

      IF [Probability] = 0 THEN

      { fixed [Account Id], [Probability], [Contract Type],

      [Recognition Date]:max([Last_Year_EOY_CV])}

      END

       

      However, this ends up summing all 20 rows and gives me $400,000. So maybe I am yet to fully understand how these LODs are working, but what could be the issue here? How can I go about doing a sum distinct with a criteria?