Aggr and Sum for KPI

[chrisbrown1]

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.

[pcv_devo]

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.