Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
lalitkgehlot89
Partner - Creator II
Partner - 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
jaumecf23
Creator III
Creator III

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
Partner - Creator II
Partner - Creator II

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.

Anonymous
Not applicable

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