topic Re: Average calculation in QlikView

Average calculation

malyadri1004 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Average calculation

HirisH_V7 — Wed, 01 May 2019 08:53:49 GMT

Check using below code,

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.

Re: Average calculation

HirisH_V7 — Wed, 01 May 2019 10:51:40 GMT

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

Re: Average calculation

malyadri1004 — Wed, 01 May 2019 11:07:19 GMT

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

Re: Average calculation

sunny_talwar — Wed, 01 May 2019 11:37:47 GMT

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

Re: Average calculation

malyadri1004 — Wed, 01 May 2019 11:54:08 GMT

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

Re: Average calculation

sunny_talwar — Wed, 01 May 2019 11:57:38 GMT

Seem to work okay for me