finding average value

[Anonymous]

Hi,

I have question like below,

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

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

Name	Date	Value	Avg.Value
A	1/1/2017AM	2	1
A	1/1/2017PM	4	-0.5
B	1/2/2017AM	6	0.17
B	1/2/2017PM	7	-0.14
C	1/3/2017AM	5	1.2
C	1/3/2017PM	11	0.545
D	1/4/2017AM	2	0.5
D	1/4/2017PM	3	-0.33

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 · Accepted solution]

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]

Thanks stefan