How to exclude max and min value in qlikview

[#1]

Hi

II have scenario,

I have data set like below

Year Location Sales

2016   Delhi    28

2016   Delhi     34

2016   delhi       45

2016    delhi      56

2016    delhi        23

2016    delhi       10

from the above data set i want to exclude max and min value

result

Year Location Sales

2016   Delhi    28

2016   Delhi     34

2016   delhi       45

2016    delhi        23

Is there any functions available to achieve this.

Thanks

Thiru

[bindu_apte]

Hi,

Hope this will help you.

(sum(Sales) - max(Sales) - min(Sales) )

[#3]

Hi Bindhu,

I didn't get you?

van you please explain?

Thiru

[tresesco]

In the script or UI chart?

[#5]

In the script

[Anonymous · Accepted solution]

Hi Thiru,

Do you want to remove it on script or on expression?

In script:

Table:
LOAD * INLINE [
    Year, Location, Sales
    2016, Delhi, 28
    2016, Delhi, 34
    2016, Delhi, 45
    2016, Delhi, 56
    2016, Delhi, 23
    2016, Delhi, 10
];

LEFT JOIN (Table)
LOAD
    Year,
    Location,
    Min(Sales)    as Sales,
    1            as MinValue
RESIDENT Table
GROUP BY Year, Location;

LEFT JOIN (Table)
LOAD
    Year,
    Location,
    Max(Sales)    as Sales,
    1            as MaxValue
RESIDENT Table
GROUP BY Year, Location;

NoConcatenate
FinalTable:
LOAD
    Year,
    Location,
    Sales
RESIDENT Table
WHERE MaxValue <> 1 and MinValue <> 1;

On expression:

Sum(Aggr(Sum({<Sales=P({<Sales={">$(=Min(Sales))<$(=Max(Sales))"}>}Sales)>}Sales),Year,Location,Sales))

Regards!

[teiswamsler]

Hi Thiru

Try this

1. Load Data

     DataTmp:

     Load

          Year,

          Location,

          Sales

     From Data;

2. Identify the Min and Max value from DataTmp

     MinMax:

     Load

          Max( Sales ) as MaxSales,

          Min( Sales ) as MinSales

     Resident DataTmp;

     Let vMin = Num(Peek('MinSales', 0, 'MinMax'));

     Let vMax = Num(Peek('MaxSales', 0, 'MinMax'));

     DROP Table MinMax;

3. Reduce Data

     Noconcatenate

     MasterData:

     Load

     *

     Resident DataTmp

          Where

               Sales > $(vMin)

               and

               Sales < $(vMax)

     ;

     DROP Table DataTmp;

Hope it will help you

/Teis

[qlikviewwizard]

DataTemp:

Load * Inline [

Year, Location, Sales

2016,Delhi,28

2016,Delhi,34

2016,delhi, 45

2016,delhi,56

2016,delhi,  23

2016,delhi, 10

];

MinMax:

Load

Max( Sales ) as MaxSales,

Min( Sales ) as MinSales

Resident DataTemp;

Let vMin = Num(Peek('MinSales', 0, 'MinMax'));

Let vMax = Num(Peek('MaxSales', 0, 'MinMax'));

DROP Table MinMax;

NoConcatenate

MasterData:

Load

*

Resident DataTemp

Where

Sales > $(vMin)

and

Sales < $(vMax);

DROP Table DataTemp;

[tresesco]

PFA

AA:

Load * Inline [

Year, Location, Sales

2016,   Delhi,   28

2016,   Delhi,    34

2016,   delhi,      45

2016,    delhi,      56

2016,    delhi,        23

2016,    delhi,       10];

Temp:

Load max(Sales) as Max,

  Min(Sales) as Min Resident AA;

Let vMax=Peek('Max');

Let vMin=Peek('Min');

NoConcatenate

Final:

Load

  *

Resident AA where not Match(Sales, $(vMax),$(vMin));

Drop Tables Temp, AA;

[effinty2112]

Hi Thiru,

                    I added a row no to your data:

Data:

Load

*,

RowNo();

LOAD * INLINE [

    Year, Location, Sales

    2016,   Delhi,    28

    2016,   Delhi,     34

    2016,   delhi,       45

    2016,    delhi,      56

    2016,    delhi,        23

    2016,    delhi,       10

];

This table gives you what you want I hope:

RowNo()	Location	Year	Sum(if(Sales > min(TOTAL Sales) and Sales < max (TOTAL Sales), Sales ))
			130
1	Delhi	2016	28
2	Delhi	2016	34
3	delhi	2016	45
5	delhi	2016	23

Regards

Andrew