Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |
Thanks stefan