# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for
Did you mean:
Creator II

## Last seven days Average value calculation on script.

Hi,

In attached data I have day wise GCV value. But against lot of dates it is blank.

Now i want last seven days average GCV value against the blank date.

GCV value for Date 12/04/2018 :-  (4299 + 4019 + 4452 + 4309 + 4358 + 4259 + 4170)/7 = 4267

GCV value for Date 13/04/2018 :-  (4267 + 4299 + 4019 + 4452 + 4309 + 4358 + 4259 )/7 = 4280

3 Replies
Highlighted
Creator III

## Re: Last seven days Average value calculation on script.

Hi,

A possible solution:

Excel:

if(IsNull(GCV),Round(RangeSum(Peek(GCV_New,RowNo()-2),Peek(GCV_New,RowNo()-3),Peek(GCV_New,RowNo()-4),Peek(GCV_New,RowNo()-5),Peek(GCV_New,RowNo()-6),Peek(GCV_New,RowNo()-7),Peek(GCV_New,RowNo()-8))/7),GCV) as GCV_New

;

GCV

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

Highlighted
Partner

## Re: Last seven days Average value calculation on script.

Hi Lalit,

Try this it works.

if(NEW_GCV=0,round((peek(NEW_GCV,-7)+peek(NEW_GCV,-6)+peek(NEW_GCV,-5)+peek(NEW_GCV,-4)+peek(NEW_GCV,-3)+peek(NEW_GCV,-2)+peek(NEW_GCV,-1)+peek(Output,-1)+peek(Output,-2))/7),0) as Output;

t:

GCV,

if(len(trim(GCV))=0,0,GCV) as NEW_GCV

FROM

(ooxml, embedded labels, table is Sheet1);

drop field NEW_GCV;

Output:

PFA below app.

Regards,

Vishal.

Highlighted
Partner

## Re: Last seven days Average value calculation on script.

Hi,

try below,

Directory;

peek:

RowNo() as Rowno,

GCV,

if((Len(trim(GCV))=0),if((len(trim(peek(GCV_New)))=0),Round(RangeSum(Peek(GCV,RowNo()-2),Peek(GCV,RowNo()-3),

Peek(GCV,RowNo()-4),Peek(GCV,RowNo()-5),Peek(GCV,RowNo()-6),Peek(GCV,RowNo()-7),Peek(GCV,RowNo()-8))/7),

Round(RangeSum(Peek(GCV_New,RowNo()-2),Peek(GCV,RowNo()-3),

Peek(GCV,RowNo()-4),Peek(GCV,RowNo()-5),Peek(GCV,RowNo()-6),Peek(GCV,RowNo()-7),Peek(GCV,RowNo()-8))/7))) as GCV_New

FROM