Qlik Community

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:

Capture.PNG.png

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)

Capture.PNG.png

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:

Capture.PNG.png

Not applicable

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

Fantastic, this rocks! Thank you so much Carly!

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

Community Browser