Calculation based on a satisfied condition in a different field?

[#1]

In the attached Excel file, I have records that are marked either with "In" or "Out" in the Exception column, with each record having a particular Delta value.

I want Qlikview to calculate 2 Delta averages based on whether a record is "In" or "Out".

The result should be 2 values: one Delta average of ALL the "In" records, one Delta average of ALL the "Out" records.

I've tried using an IF() statement: Avg(If(Exception='Out',Delta,0)) but the answer is wrong.

Ideally, if Qlikview were averaging the "Outs" and came across an "In" it would simply skip it.

Any help is appreciated.

Thanks,

Naveed

[#2]

This should work

2 expressions:

avg( {<Exception = {'In'}>} Delta)

avg( {<Exception = {'Out'}>} Delta)

[#3]

YES! That worked great when I put the calculation in a pivot table. But is there a way I could put something like this in my load script? Qlikview doesn't seem to like the way the calculation is formatted in your answer.

Thank you for your help!

[#4 · Accepted solution]

You could create two seperate fields to sum:

LOAD [PO Number],
     Delta,
     Grouping,
     if( Exception = 'In', Delta) as In_Delta,
     if( Exception = 'Out', Delta) as Out_Delta,
     Exception
FROM
[Sample Exception Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Then just average the fields:

[#5]

Fantastic, this rocks! Thank you so much Carly!

[oscar_ortiz]

You can add aggregation in your script, you just need to be able to use the Group By to properly aggregate your data.

For Example

E2:

LOAD
[PO Number],
Grouping,
Exception,
If( Exception = 'In', Avg( Delta )) as In,
If( Exception = 'Out', Avg( Delta )) as Out
Resident
E1
Group By
[PO Number],
Grouping,
Exception
;