# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Creator II

## Comparing to fields in Set Analysis

Hey All,

Here is my question I need to calculate the sum of Amount if the "From ID" is Deferent Then "To ID" Example :

From ID      To ID       Amount

1                  1            \$1,000

1                   2           \$3,000

1                   5           \$4,000

Aggregation  OUT:

From ID            Total Amount

1                           \$7,000

Hope you can help,

Thank you.

8 Replies
Partner

Hi,

This can help:

Sum(If([From ID] <> [To ID], Amount))

Saludos.

Champion II

Two ways to do this

Option1:

use expression

= SUM(if( FromID <> ToID , Amount))

Option2:

Create key in script that identifies each row separately/uniquely

LOAD * , Rowno() as RowKEY INLINE [

From ID    ,To ID ,      Amount

1                ,  1    ,        \$1,000

1                 ,  2  ,         \$3,000

1                  , 5 ,          \$4,000

];

Then in chart use Expression

=Sum( {< RowKEY = {"=FromID<>ToID"} >} Amount)

Partner

Hallo,

i would create a flag in the script using the folluwing code

[From ID],

[To ID],

Amount,

If([From ID] <> [To ID], 1, 0) as Flag

If you have this Flag field you can use this expression

Sum(Amount * Flag)

Regards

Sebastian

Partner

Hello,

What you are looking for is this: sum({<[To ID]-=P([From ID])>} Amount)

The result:

Hope it hels,

Andrei

Master

Hi Andrei,

Try your expression with this data:

From ID,      To ID,      Amount

1,                  1,            \$1000

1,                  2,          \$3000

2,                  3,          \$4000

];

The correct answer should be \$7,000 but your expression, sum({<[To ID]-=P([From ID])>} Amount), will return \$4,000. The second line should be included in the sum because for this record [From ID] <> [To ID]. Your expression excludes all Amounts that have a [To ID] value that appears as a value for [From ID] for any record.

Kind regards

Andrew

Master

Hi Mario,

You have lots of choices, here's one more:

=sum({\$<Amount = {"= [From ID] <> [To ID]"}>}Amount)

Regards

Andrew

Champion II

this will fail when the Amount value is similar to another row which actually satisfies the condition

for example try with

From ID,      To ID,      Amount

1,                  1,            \$3000

1,                  2,          \$3000

2,                  3,          \$4000

];

Master

Hi Vineeth,

You're right. I really need a unique ID. Sloppy of me to think Amount could be used.

Thanks

Andrew

Community Browser