Above/previous

[Anonymous]

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.

• ETLDate. It's only a date.
• round(ETLDate - previous(ETLDate)) as DaysBetweenRep
• if(SN=previous(SN), 'False', 'True') as Last
• inkValue. Only a number.

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

[sunny_talwar]

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?

[Anonymous]

SN
12345
12345
12345
12345
12345
56789

I have another column called SN, in this case:

[sunny_talwar]

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?

[Anonymous]

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)

[sunny_talwar · Accepted solution]

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

];

[Anonymous]

Thanks you Sunny, it works perfect.