Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
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.

Untitled.png

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
Creator III

Re: Last seven days Average value calculation on script.

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,

Partner
Partner

Re: Last seven days Average value calculation on script.

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:

Capture5.PNG

PFA below app.

Regards,

Vishal.

Highlighted
Partner
Partner

Re: Last seven days Average value calculation on script.

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

rage.PNG