3 Replies Latest reply: Nov 1, 2017 1:25 PM by Lee West

# If statement on summed total, not a sumif

I'm new to Qlik and running into a problem that I'm sure has a simple solution...

I have a data set of obligation documents that is compiled monthly. These documents are sometimes modified upward or down in value.

Example: Ob doc #1234 may be obligated for \$75K in January, modded with an additional \$325K in April, a \$25K reduction in May, and then additional \$50K in October.  So, sum total = \$425K over the course of the year and 1 component piece that exceeds this alone.

I have a formula in a table to identify obligations greater than \$250K. It works with some, but not all. It is excluding a number of documents that clearly exceed this threshold when looking at the summed totals. The example above should be identified as being over \$250K but it is not working the way I want it to. The summed total is clearly higher in the table. It seems to be looking up the first instance the formula encounters, but I'm guessing here. That formula is:

=If([Oblig. (Cum)] > 249999.99, '\$250K','no')

So I attempted the obvious fix, to me at least:

=If(Sum([Oblig. (Cum)]) > 249999.99, '\$250K','no')

Qlik doesn't report any error with the modified formula, but when I apply the change to the table I get an error of "Invalid Dimension".

What am I doing wrong?

• ###### Re: If statement on summed total, not a sumif

To use this as dimension, you would probably need to use Aggr() function.... Try like this

=Aggr(If(Sum([Oblig. (Cum)]) > 249999.99, '\$250K','no'), [Document #])

• ###### Re: If statement on summed total, not a sumif

Thanks. This worked.

• ###### Re: If statement on summed total, not a sumif

Aggregations in dimension should occur with an Aggr function.