Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
chrisbrown1
Contributor III
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)
1 Reply
pcv_devo
Partner - Contributor II
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.