topic Re: Above/previous in QlikView

Above/previous

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

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

Re: Above/previous

sunny_talwar — Tue, 29 Nov 2016 12:06:38 GMT

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?

Re: Above/previous

Anonymous — Tue, 29 Nov 2016 12:15:44 GMT

SN
12345
12345
12345
12345
12345
56789

I have another column called SN, in this case:

Re: Above/previous

sunny_talwar — Tue, 29 Nov 2016 12:18:33 GMT

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?

Re: Above/previous

Anonymous — Tue, 29 Nov 2016 12:25:47 GMT

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)

Re: Above/previous

sunny_talwar — Tue, 29 Nov 2016 12:55:52 GMT

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

];

Re: Above/previous

Anonymous — Wed, 30 Nov 2016 07:03:44 GMT

Thanks you Sunny, it works perfect.