Last seven days Average value calculation on script.

[lalitkgehlot89]

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

[jaumecf23]

Hi,

A possible solution:

Excel:

Load*,

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

;

LOAD DATE,

     GCV

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

[vishalarote]

Hi Lalit,

Try this it works.

load *,

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:

LOAD DATE,

    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.

[Anonymous]

Hi,

try below,

Directory;

peek:

LOAD DATE,

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

[..\Downloads\GCV.xlsx]

(ooxml, embedded labels, table is Sheet1);