topic Re: Last seven days Average value calculation on script. in QlikView

Last seven days Average value calculation on script.

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

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

Re: Last seven days Average value calculation on script.

jaumecf23 — Fri, 17 Aug 2018 10:00:48 GMT

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,

Re: Last seven days Average value calculation on script.

vishalarote — Mon, 20 Aug 2018 06:43:24 GMT

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;

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.

Re: Last seven days Average value calculation on script.

Anonymous — Mon, 20 Aug 2018 07:40:56 GMT

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);