Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nslemmons
Contributor II
Contributor II

Average based on standard deviation threshold

Hello all.

I am working on a dashboard to evaluate transportation rates and provide the end user with an average cost based on selections made.

Selections are basically from and to and a few others.

What I want to do is evaluate the records based on selections and provide an average. That's easy enough. But I want to evaluate the records first and throw out any records with a cost that is outside the standard deviation. I'm trying to make the average as accurate as possible and want to avoid outliers increasing or decreasing the average artificially.

The data has a field called [Actual Cost] that I need to evaluate for the average. There are also records with 0 that I would want to exclude as well.

Seems I would need an IF statement in here. Just not sure on how the syntax would work.

These are the fields in the data load I'm working with.

     [FO ID],

     [FOI Origin],

     [FU ID],

     [SO Number],

     [DV Number],

     [PO Number],

     [Bulk/Pack],

     [Bulk/Pack Desc],

     Division,

     [Division Desc],

     [Actual Departure Date],

     [FO Stg Srce Loc],

     [From Name],

     [From City],

     [From State],

     [From Zip],

     [From City State],

     [FO Stg Dest Loc],

     [To Name],

     [To City],

     [To State],

     [To Zip],

     [To City State],

     [Executing Carrier],

     [Material Group],

     Material,

     [Material Desc],

     [Actual Cost],

     [Est Cost],

     Miles,

     [Stage Miles],

     [Gross Wgt],

     [Item Gr Wgt],

     [Stem Mi]

Any help is appreciated.

Thanks,

Nate

1 Reply
Anil_Babu_Samineni

Perhaps this?

IF Statement

If([Actual Cost] <> '0' or Is NotNull([Actual Cost]),Avg([Actual Cost]))

Set Analysis

Avg({<[Actual Cost] = {'>0'}>} [Actual Cost])

OR

Avg({<[Actual Cost] = {"=Avg([Actual Cost])>0"}>} [Actual Cost])

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)