topic Calculation based on a satisfied condition in a different field? in QlikView

Calculation based on a satisfied condition in a different field?

Wed, 19 Nov 2014 15:41:20 GMT

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?

Wed, 19 Nov 2014 16:03:53 GMT

This should work

2 expressions:

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

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

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

Wed, 19 Nov 2014 16:20:31 GMT

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!

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

Wed, 19 Nov 2014 16:34:07 GMT

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:

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

Wed, 19 Nov 2014 16:40:16 GMT

Fantastic, this rocks! Thank you so much Carly!

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

oscar_ortiz — Wed, 19 Nov 2014 16:41:51 GMT

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
;