5 Replies Latest reply: Nov 19, 2014 11:41 AM by Oscar Ortiz

# Calculation based on a satisfied condition in a different field?

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

• ###### Re: Calculation based on a satisfied condition in a different field?

This should work

2 expressions:

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

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

• ###### Re: Calculation based on a satisfied condition in a different field?

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.

• ###### Re: Calculation based on a satisfied condition in a different field?

You could create two seperate fields to sum:

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:

• ###### Re: Calculation based on a satisfied condition in a different field?

Fantastic, this rocks! Thank you so much Carly!

• ###### Re: Calculation based on a satisfied condition in a different field?

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:

[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
;