10 Replies Latest reply: Oct 12, 2012 2:20 PM by Trevor Roth

# if-statement expression not aggregating correctly

Can someone help me with a percentage calculation that is not aggregating correctly? My expressions are using if-statements to filter out rows in a chart accordingly. [DPC %] is calculating correctly for each individual line in the chart, but the total for the expression is wrong. The total for [DPC %] is including rows in the chart are filtered out, so it is ignoring the if-statement. I believe this is because my expressions use sum(x) inside the if-statement – but I don’t know how else to do this expression. Any ideas?

Expressions:

Net Sales:

=if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

sum([Net Sales]))

DPC \$

=if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

sum([DPC]))

DPC %

=

if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

sum([DPC]))

/

if(vSalesFilter_UseTheFilterFlag='Yes',

if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

sum([Net Sales]))

• ###### Re: if-statement expression not aggregating correctly

Try this.

• ###### Re: if-statement expression not aggregating correctly

Using set analysis, it shouldn't matter how many records you have. What part is not working?

• ###### Re: if-statement expression not aggregating correctly

If you think through how the expression total is evaluated, it first looks at the if-statement/s (which are true) then evaluates the sum(x) portion. At this point it would be a sum of all records, meaning the if-statement is negated. Thus, the rest of the chart works great - but the expression total is always a sum of all records regardless. This is why I think I need to embed the if logic within the sum statement, but I can quite figure out the best way of doing that.

• ###### Re: if-statement expression not aggregating correctly

That was why I renamed your Net Sales column to Net Sales  (added one extra space) and then used your column headers as the expression:

[DPC \$]/[Net Sales ]

This way it will only evaluate based on the sums in the two columns.  Did you try that? Sorry, I guess I should have made that more clear.

• ###### Re: if-statement expression not aggregating correctly

That's a really good idea. I couldn't get it to work however. It is still calculating based on all rows vs. only the visible rows. I added a 2 on the column names instead of your space to make the rename more visible.

DPC% 2 expression:

=[DPC \$2] / [Net Sales 2]

• ###### Re: if-statement expression not aggregating correctly

Can you upload a sample file with customer data scrambled out? I tried a test document with a ton of records and it was working.

• ###### Re: if-statement expression not aggregating correctly

You'll notice on this version that when the filter is turned off it works fine, but when the filter is turned on it does not work.

Filter off:

the DPC%2 total should be 33% (1,135,207,375 / 3,462,945,698 = 0.3278)

Filter on:

the DPC%2 total should be 26% (282,682,342 / 1,077,512,423 = 0.262)

• ###### Re: if-statement expression not aggregating correctly

See if this works.

• ###### Re: if-statement expression not aggregating correctly

That works! Thanks - that was exactly what I was looking for!

• ###### Re: if-statement expression not aggregating correctly

Thanks rebeccad - I think this works with the example data because of the small number of records. When the data scales up to millions of rows this solution does not work.

I think I need to put the aggregation outside the if statement (or use a set expression instead), but I can get it to work.