Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average of previous n values in script

Hi all,

I'm pretty new to Qlikview but picking it up quickly but this query has me stumped, even after trawling the internet.

I have a table like the one below. What I would like to do is replace - with the average of the previous 2 weeks values for that location (in reality this will be the previous 6 values but just for the example I've selected 2 values). For example:

1) For location A in week 3 the value would be 0.4 ((0.2 + 0.6)/2).

2) For location B in week 2, because there is only one prior value in week 1 for location B (i.e. we would ignore week 3 as that is after week 2 which has the missing value), it would become that figure (0.3). Note the order of the data is not in location/week order to start with.

3) For location A week 4, the previous 2 values in the raw data are 0.6 and -. However, from example 1 above, the - is replaced with 0.4 now, so - would become 0.5 ((0.6+0.4)/2).

Hope that makes sense? I need to do this in the script as there are millions of rows of data and Qlikview calculating this takes a long time in charts. I don't want this value to change based on selections made anywhere else at all.

I've read things above peek and rangesum but can't get either to work. Any help is very much appreciated!

Thanks in advance

Kev

Location     Week     Unique No     Value

A               1               134               0.2

B               1               213               0.3

A               2               746               0.6

C               1               748               0.9

B               3               643               0.5

C               2               837               0.1

B               2               6478               -

A               3               7348               -

A               4               748                 -

7 Replies
sujeetsingh
Master III
Master III

You can move with the Previous() function in Qlikview.

The best way is to create this based on script.

You need to create this by assigning month as rank as

See the sample

tresesco
MVP
MVP

May be like:

T1:
LOAD * inline [
Location,Week,"Unique No",Value
A,1,134,0.2
B,1,213,0.3
A,2,746,0.6
C,1,748,0.9
B,3,643,0.5
C,2,837,0.1
B,2,6478,-
A,3,7348,-
A,4,748,-
]
;

T2:
NoConcatenate
Load
     
AutoNumber(RowNo(),Location) as Seg,
     
Location,
     
Week,
     
Value
Resident T1 Order By Location, Week;
T3:
Load
           *,
          
If(Seg<2, Value,RangeAvg(Peek('Value',-2), Peek('NewValue',-1))) As NewValue

Resident T2;

Drop Table T1, T2;

Not applicable
Author

Hiya,

Thanks for your response. It doesn't seem to be doing exactly what I need unless I'm missing something? I've taken the liberty of adjusting your load to reflect the data above and changed your objects accordingly (but not deleted any). In the table produced I need:

the third line 'value' to equal 0.4 (average of the previous two lines shown).

the fourth line 'value' to equal 0.5 (average of 0.6 and 0.4 as calculated above)

the sixth line 'value' to equal 0.3 (average of previous two values for location B. As there is only one previous week of data for location B it should therefore be 0.3).

As you know I would like this in the script instead of calculating it in tables/charts as there are millions of rows of data in reality.

Many thanks for your help!

Kev

Not applicable
Author

Hi Tresesco,

Thanks for your response. Looks close! I've edited the attached to show what I need ('Wanted' column) and an explanation ('Description' column). Hope that helps.

Would it involve some sort of sum of previous two peeks with location as a criteria (assumed sorted by location and week as you have done) and divide by count of previous instances of location up to max of 2 (if that makes sense!!)?

I've attached an excel document with a 'calculation' column showing how I would do this in Excel (I'm much more familiar with excel!).

Cheers

Kev

tresesco
MVP
MVP

Just add one more condition:

If(Seg<2 or Value<>'-', Value,RangeAvg(Peek('Value',-2), Peek('NewValue2',-1))) As NewValue2

Not applicable
Author

Brilliant! This looks to be doing the job really well!

One final query though is if Week 1 is - and I wanted to do the same but look forward (so average of weeks 2 and 3 where value <> '-' ) is that feasible?

Not applicable
Author

Hi Sujeet,

                 I had a similar requirement where i need to average out the inventory for (current week+ last week)/2. so i created Previous(inventory). Looks fine when i preview it in the data model but when i create a table box or pivot table the values are not as expected. Can you please help me with this issue?