# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor III

## Aggr and Sum for KPI

I am trying to create a KPI from a table. The table looks like this:

ID     Contract     PIID     TO     Obl

1       273               -             -         \$3000

2        -                   39           -         \$5400

2         -                  39          T36    \$5000

2        -                   39          T45    \$400

2       -                    32          -           \$500

2      -                     32           S32     \$100

2     -                      32           S35     \$400

3     467                -               -           \$700

I have filters for the Dimensions:  ID, Contract, PIID, TO. When a selection is made within the filters I want to trigger the KPI.

The KPI would be the sum of Obl by ID. The issue I have is the aggr when there are multiple lines for an ID.  For ID 2 I want to get the sum of just the entries where Contract IsNull and TO IsNull. Not the other rows within the corresponding ID.

I have tried for the KPI:

=Sum(Aggr(If (not IsNull ([Contract]), Obl,

If(not IsNull(TO), Obl, If(IsNull([Contract]) and IsNull([TO]), Obl))), ID))

It works when there are not multiple entries for a filter. Any help would be appreciated.

Labels (2)

• ### Set Analysis

Partner - Contributor II

Hi Chrisbrown1,

If I have understood the premises correctly, the result of that example should be \$9600. The problem is in the Aggr. Try this:

=Sum(Aggr(If (not IsNull ([Contract]),Obl,If(not IsNull(TO),Obl,If(IsNull([Contract]) and IsNull([TO]),Obl))), ID,Contract,TO))

You're basing your calculations on a table, but when building the aggr, you aren't taking into account the structure of that same table (I mean, the rest of the fields). To fix this, you need to add more StructuredParameter.

Tags
Community Browser