Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## 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?

1 Solution

Accepted Solutions
MVP

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 #])

3 Replies
MVP

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 #])

Creator III

Aggregations in dimension should occur with an Aggr function.

Anonymous
Not applicable
Author

Thanks. This worked.

Tags
Community Browser