Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I would like to calculate the Rolling 3 days average in Qlikview script based upon the File date.
If we take it the File Date - 01/05/2017, it should calculate the average of last 3 days like as sum(684,458,235)/3 =459
Here is My data set.
File Date | Size |
01-01-2017 | 121 |
01-02-2017 | 235 |
01-03-2017 | 458 |
01-04-2017 | 684 |
01-05-2017 | 453 |
01-06-2017 | 159 |
01-07-2017 | 147 |
01-08-2017 | 596 |
01-09-2017 | 999 |
01-10-2017 | 100 |
01-11-2017 | 158 |
Please let me know if you have suggestions.
Regards,
Shakila D
In your chart syntax, you can try with :
RangeAvg(Below(Size,-1,3))
I can't manage to use Below() in script but apparently this function works in script.
Yes, I have to implement this logic in script. Is any other way to implement?
Why are you looking another way?
what if there is just 2 Previous day?
Maybe something like the below
a:
LOAD * INLINE [
FileDate, Size
01/01/2017, 121
01/02/2017, 235
01/03/2017, 458
01/04/2017, 684
01/05/2017, 453
01/06/2017, 159
01/07/2017, 147
01/08/2017, 596
01/09/2017, 999
01/10/2017, 100
01/11/2017, 158
];
for i = 1 to 3
load
FileDate as FileDate1 ,
date(FileDate-$(i)) as FileDate
Resident a ;
NEXT
And use the Filedate1 in the chart to do the average calucaltion
Hi Shakila,
Try:
Data:
Load
[File Date],
Size,
Rangesum(Size,Peek(Size),Peek(Size,-2)) as AccSize;
LOAD * INLINE [
File Date, Size
01-01-2017, 121
01-02-2017, 235
01-03-2017, 458
01-04-2017, 684
01-05-2017, 453
01-06-2017, 159
01-07-2017, 147
01-08-2017, 596
01-09-2017, 999
01-10-2017, 100
01-11-2017, 158
];
giving:
File Date | Size | AccSize |
---|---|---|
01-01-2017 | 121 | 121 |
01-02-2017 | 235 | 356 |
01-03-2017 | 458 | 814 |
01-04-2017 | 684 | 1377 |
01-05-2017 | 453 | 1595 |
01-06-2017 | 159 | 1296 |
01-07-2017 | 147 | 759 |
01-08-2017 | 596 | 902 |
01-09-2017 | 999 | 1742 |
01-10-2017 | 100 | 1695 |
01-11-2017 | 158 | 1257 |
Regards
Andrew