Average calculation

[malyadri1004]

Below is the data set , I need average below table for each vintage. Below is the only one vintage I want to average the value by excluding last value i.e: 5 .After excluding I need average in qlik.

I can able to get total average by using below formula

Avg( Aggr(rangesum( above(-sum(Value),0,Max(Vintage))),Vintage))

Total Avg : 7.125

After excluding 5 , Avg is : 7.83 ( I need out put this)

Can any one help me on this

Vintage	Value
A	10
A	9
A	4.5
A	5

[HirisH_V7]

Check using below code,

Spoiler

Temp:
Load *
INLINE [
Vintage, Value
A, 10
A, 9
A, 4.5
A, 5,
B, 1,
B,4,
];

Temp2:
LOAD *,
if(Previous(Vintage)=Vintage ,peek('Row')+1,1) As Row
Resident Temp Order by Vintage;

NoConcatenate

Temp3:
Load Max(Row) as Row,Vintage,Max(Row)&Vintage as key1 Resident Temp2 Group By Vintage;
Concatenate
LOAD Vintage, Value,Row Resident Temp2 Where NOT Exists(key1,Row&Vintage);

Drop Field Row,key1;

Main:
Load Avg(Value) as Avg, Vintage Resident Temp3 Group By Vintage;

DROP Tables Temp,Temp2,Temp3;

Try this, its done in script.

PFA app For ref.

[HirisH_V7]

In UI Level some thing like this,

Script:

Temp:
Load *
INLINE [
Vintage, Value
A, 10
A, 9
A, 4.5
A, 5,
B, 1,
B,4,
];

Temp2:
LOAD *,if(Previous(Vintage)=Vintage ,peek('Row')+1,1)&Vintage as Supress,
if(Previous(Vintage)=Vintage ,peek('Row')+1,1) As Row
Resident Temp Order by Vintage;
DROP Table Temp;
Exit SCRIPT;

In UI:

Avg({<Supress-={$(=Concat(Aggr(chr(39)&max(Row)&Vintage&chr(39),Vintage),','))}>}Value)

PFA 

For ref

[malyadri1004]

Hi Harish, Can you please provide file in .qvf. As I am unable to open your file my server is Qliksense

[sunny_talwar]

Wow, that is one complicated expression to calculate Average... did this not work for a simple average
Avg(Value)?

For the main question, do you always want to exclude the last value within a specific vintage? May be this
Avg(Aggr(If(RowNo() <> NoOfRows(), Value), Vintage, Value))

[malyadri1004]

Hi Sunny Talwar,  Yes I need to exclude the last value within a specific vintage , But your formula didn't work in Qliksense

[sunny_talwar · Accepted solution]

Seem to work okay for me