Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have this DB in qlikview and I need to implement a new variable called MovingAverage.
ETLDate | DaysBetweenRep | Last | InkValue |
---|---|---|---|
2015-07-29 | 1 | False | 2 |
2015-07-30 | 1 | False | 6.85 |
2015-08-01 | 2 | False | 8 |
2015-08-02 | 1 | False | 9 |
2015-09-14 | 43 | False | 11.3 |
2015-09-15 | 1 | True | 5 |
First, i'm going to explain the variables i have.
So, I want to create a new variable (MovingAverage) in script to do the following (Counting that I am in the fourth line):
if( Above(Last,1,3)<>'TRUE' and rangesum(above(DaysBetweenRep,0,3))<>'0', (inkValue-above(inkvalue,3,1))/above(days,0,3)*30, -1)
if(C1<>True and C2<>True and C3<>True and (B2+B3+B4)<>0, (D4-D1)/(B2+B3+B4)*30, -1)
I know that the "above" function doesn't work in script, i've tried with the function "previous"...
Can you help me?
Thanks
May be this:
Table:
LOAD *,
If(Previous(Last) = 'False' and Peek('Last', -2) = 'False' and Peek('Last', -3) = 'False' and Last = 'False' and
Previous(DaysBetweenRep) <> 0 and Peek('DaysBetweenRep', -2) <> 0 and Peek('DaysBetweenRep', -3) <> 0,
(InkValue - Peek('InkValue', -3))/RangeSum(DaysBetweenRep, Previous(DaysBetweenRep), Peek('DaysBetweenRep', -2)) * 30, -1) as MovingAverage;
LOAD *,
Alt(Round(ETLDate - previous(ETLDate)), 1) as DaysBetweenRep;
LOAD * INLINE [
ETLDate, SN, InkValue, Last
2015-07-29, 12345, 2, False
2015-07-30, 12345, 6.85, False
2015-08-01, 12345, 8, False
2015-08-02, 12345, 9, False
2015-09-14, 12345, 11.3, False
2015-09-15, 56789, 5, True
];
Column Last, how is that getting calculated? Also would you be able to share the raw data behind this and also share expected output for the new MovingAverage column?
SN |
---|
12345 |
12345 |
12345 |
12345 |
12345 |
56789 |
I have another column called SN, in this case:
So essentially you have three columns in your data base
1) ETLDate
2) SN
3) InkValue
For the above dataset, what would be the value for Moving Average column?
For the fourth line it would be (9-2)/(1+2+1) *30= 42
The fifth: (11.3 - 6.85)/(43+1+2)*30= 2.90
and the sixth one it would be -1, because Last is True (it has changed teh serial number)
May be this:
Table:
LOAD *,
If(Previous(Last) = 'False' and Peek('Last', -2) = 'False' and Peek('Last', -3) = 'False' and Last = 'False' and
Previous(DaysBetweenRep) <> 0 and Peek('DaysBetweenRep', -2) <> 0 and Peek('DaysBetweenRep', -3) <> 0,
(InkValue - Peek('InkValue', -3))/RangeSum(DaysBetweenRep, Previous(DaysBetweenRep), Peek('DaysBetweenRep', -2)) * 30, -1) as MovingAverage;
LOAD *,
Alt(Round(ETLDate - previous(ETLDate)), 1) as DaysBetweenRep;
LOAD * INLINE [
ETLDate, SN, InkValue, Last
2015-07-29, 12345, 2, False
2015-07-30, 12345, 6.85, False
2015-08-01, 12345, 8, False
2015-08-02, 12345, 9, False
2015-09-14, 12345, 11.3, False
2015-09-15, 56789, 5, True
];
Thanks you Sunny, it works perfect.