# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Contributor

## Average calculation

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
1 Solution

Accepted Solutions
MVP

Seem to work okay for me

6 Replies
Master

Check using below code,

Spoiler

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

Temp2:
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
“Aspire to Inspire before we Expire!”
Master

In UI Level some thing like this,

Script:

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

Temp2:
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

HirisH
“Aspire to Inspire before we Expire!”
Contributor
Author

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

MVP

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))

Contributor
Author

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

MVP

Seem to work okay for me