Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
malyadri1004
Contributor
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

VintageValue
A10
A9
A4.5
A5
  
1 Solution

Accepted Solutions
sunny_talwar

Seem to work okay for me

image.png

View solution in original post

6 Replies
HirisH_V7
Master
Master

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

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

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

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

sunny_talwar

Seem to work okay for me

image.png

View solution in original post