topic Re: finding average value in QlikView

finding average value

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

Hi,

I have question like below,

from above table I need to find the average value like below table,

here i applied one formula manually to find the avg value,

By using this below formula to find Avg value's

finding AM Avg Value :

PM-AM\AM---->4-2/2=1

finding PM value:

AM-PM/PM----->2-4/4=-0.5

In qlik it will be possible to find the Avg.Value dynamically. I am looking for expression or script wise to get the Avg.Value dynamically.

Regards,

Yuvaraj

swuehl — Mon, 17 Apr 2017 14:51:35 GMT

Looks more like a relative increase / decrease than an average value.

Maybe like

Set DateFormat = 'M/D/YYYY';

LOAD Name, Date as DateOrig, Left(Date,Index(Date,'/',2)+4) as Date, Right(Date,2) as AMPM, Value;

LOAD * INLINE [

Name, Date, Value

A, 1/1/2017AM, 2

A, 1/1/2017PM, 4

B, 1/2/2017AM, 6

B, 1/2/2017PM, 7

C, 1/3/2017AM, 5

C, 1/3/2017PM, 4

D, 1/4/2017AM, 2

D, 1/4/2017PM, 3

];

and then a table chart with Name, DateOrig as dimensions and as expression

=If(AMPM='AM',

Only({<AMPM = {PM}>} TOTAL<Name,Date> Value) / Only({<AMPM = {AM}>} TOTAL<Name,Date> Value)-1,

Only({<AMPM = {AM}>} TOTAL<Name,Date> Value) / Only({<AMPM = {PM}>} TOTAL<Name,Date> Value) -1

)

Name	DateOrig	Value	Increase / Decrease

A	1/1/2017AM	2	1,00
A	1/1/2017PM	4	-0,50
B	1/2/2017AM	6	0,17
B	1/2/2017PM	7	-0,14
C	1/3/2017PM	4	0,25
C	1/3/2017AM	5	-0,20
D	1/4/2017AM	2	0,50
D	1/4/2017PM	3	-0,33

Anonymous — Mon, 17 Apr 2017 19:07:22 GMT

Thanks stefan