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

# Understanding LOD Expressions and SUM Distinct

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?

• ###### 1. Re: Understanding LOD Expressions and SUM Distinct

Hey Rahul,

It sounds like you might be jumping between aggregation types in the view. When you add in the if statement.

Try this (Or potentially just change the aggregation on your previous field to ATTR):

IF MIN([Probability]) = 0 THEN

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

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

END

1 of 1 people found this helpful
• ###### 2. Re: Understanding LOD Expressions and SUM Distinct

Hey Derrick,

Many thanks! That worked. I was also able to get it to work by doing sum instead of attr; however, in both cases, my column grand total does not work. When I use attr, it just shows * for column grand total. When I use sum, it computes it, but at the sum distinct level. How can I go about doing a sum of all values in the column grand total? I know I could do total all using sum, but that would then mess up other calculations. Anyway to do total 1 column using sum?

• ###### 3. Re: Understanding LOD Expressions and SUM Distinct

Hey Rahul,

It sounds like you might want to remove some of the "FIXED" pieces for your grand total.

Maybe even something like this:

ATTR({ fixed [Probability] :max([Last_Year_EOY_CV])})