# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

## 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

1 Solution

Accepted Solutions
Not applicable

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

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 Replies
Not applicable

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

This should work

2 expressions:

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

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

Not applicable

## 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.

Thank you for your help!

Not applicable

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

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:

Not applicable

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

Fantastic, this rocks! Thank you so much Carly!

Valued Contributor

## 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:

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
;